Replacing variable values with proc sql

  • Permalink
  • submit to reddit
  • Email
  • Follow


Hi SAS-L users!

I was wondering if there is a possibility to replace values of a variable
with proc sql. The data step would look like the following:

data test;
  set test;
  if var=' ' then var='No Value';
run;

The if and then statement is what I am looking for in proc sql (without
creating a macro).

Thanks a lot for any comments.

Hadassa
0
Reply dassybr (6) 2/8/2006 8:16:37 AM

See related articles to this posting


On Wed, 8 Feb 2006 03:16:37 -0500, Hadassa Brunschwig <dassybr@HOTMAIL.COM>
wrote:

>Hi SAS-L users!
>
>I was wondering if there is a possibility to replace values of a variable
>with proc sql. The data step would look like the following:
>
>data test;
>  set test;
>  if var=' ' then var='No Value';
>run;
>
>The if and then statement is what I am looking for in proc sql (without
>creating a macro).
>
>Thanks a lot for any comments.
>
>Hadassa

Yes there are a few ways:


data test;
aa='1';
bb='2';
cc=' ';
run;

missing values are easiest replaced
with the coalesce function, like this:

proc sql;
 select aa, bb, coalesce(cc,'Missing')
 from test;
quit;

Case expressions can also do that, and a lot more.
As a general rule, check out the documentation!

proc sql;
 select aa, bb,
  case
   when cc is missing then 'Missing'
   else cc
  end as cc
 from test;
quit;
0
Reply sas__l (62) 2/8/2006 8:41:57 AM

Hi Hadassa Brunschwig.

Here is the SQL code for replacing data


data test;
infile cards missover;
input id name$ city$ ;
cards;
1 A YN
2 B BK
3 C IN
4 D
5 Y   ;RUN;

proc sql;
create table test as
select id, name, coalesce(city,'No Info') 
 from test; 
quit;

0
Reply ganesh.makireddy (3) 2/8/2006 10:24:29 AM

Hadassa:

  You can use the following:

proc sql;
  Update test
      set var='No Value'
      where var is missing;
quit;

J S Huang
1-515-557-3987
fax 1-515-557-2422

>>> Hadassa Brunschwig <dassybr@HOTMAIL.COM> 2/8/2006 2:16:37 AM >>>
Hi SAS-L users!

I was wondering if there is a possibility to replace values of a
variable
with proc sql. The data step would look like the following:

data test;
  set test;
  if var=' ' then var='No Value';
run;

The if and then statement is what I am looking for in proc sql
(without
creating a macro).

Thanks a lot for any comments.

Hadassa
0
Reply jiann-shiun.huang (548) 2/8/2006 1:55:14 PM

Hadassa,

You looking for the coalesce function, incidently you can use it in the data
step too.



Toby Dunn





From: Hadassa Brunschwig <dassybr@HOTMAIL.COM>
Reply-To: Hadassa Brunschwig <dassybr@HOTMAIL.COM>
To: SAS-L@LISTSERV.UGA.EDU
Subject: Replacing variable values with proc sql
Date: Wed, 8 Feb 2006 03:16:37 -0500


Hi SAS-L users!

I was wondering if there is a possibility to replace values of a variable
with proc sql. The data step would look like the following:

data test;
   set test;
   if var=' ' then var='No Value';
run;

The if and then statement is what I am looking for in proc sql (without
creating a macro).

Thanks a lot for any comments.

Hadassa
0
Reply tobydunn (6019) 2/8/2006 2:29:38 PM
comp.soft-sys.sas 130993 articles. 26 followers. Post

4 Replies
508 Views

Similar Articles

[PageSpeed] 52


  • Permalink
  • submit to reddit
  • Email
  • Follow


Reply:

Similar Artilces:

Re: Replacing variable values with proc sql
On Wed, 8 Feb 2006 03:16:37 -0500, Hadassa Brunschwig <dassybr@HOTMAIL.COM> wrote: >Hi SAS-L users! > >I was wondering if there is a possibility to replace values of a variable >with proc sql. The data step would look like the following: > >data test; > set test; > if var=' ' then var='No Value'; >run; > >The if and then statement is what I am looking for in proc sql (without >creating a macro). > >Thanks a lot for any comments. > >Hadassa Yes there are a few ways: data test; aa='1'; bb='2'; cc=' &#...

Re: Replacing variable values with proc sql #2
> Date: Wed, 8 Feb 2006 04:25:49 -0500> From: dassybr@HOTMAIL.COM> Subject:= Re: Replacing variable values with proc sql> To: SAS-L@LISTSERV.UGA.EDU> >= Thanks for your comment!> The problem is that I have a dataset with many v= ariables and it would be> crazy to list them all. So if I do:> > create tab= le test;> select *, case> when cc is missing then 'Missing'> from dataset;>= quit;> > I get a warning that cc already exists in the dataset(which is co= rrect) and> the missing value of cc is not replaced.> (Bummer...)> > A...

Re: Replacing variable values with proc sql #3
Thanks for your comment! The problem is that I have a dataset with many variables and it would be crazy to list them all. So if I do: create table test; select *, case when cc is missing then 'Missing' from dataset; quit; I get a warning that cc already exists in the dataset(which is correct) and the missing value of cc is not replaced. (Bummer...) Any comments on that ;-)? Hi Hadassa Brunschwig, Use this code and let me know if you have any problem...., I hope it vl use to you proc sql; create table test as select *, coalesce(city,'No Info') from test; quit; data...

Re: Replacing variable values with proc sql #4
> Date: Wed, 8 Feb 2006 04:25:49 -0500> From: dassybr@HOTMAIL.COM> Subject:= Re: Replacing variable values with proc sql> To: SAS-L@LISTSERV.UGA.EDU> >= Thanks for your comment!> The problem is that I have a dataset with many v= ariables and it would be> crazy to list them all. So if I do:> > create tab= le test;> select *, case> when cc is missing then 'Missing'> from dataset;>= quit;> > I get a warning that cc already exists in the dataset(which is co= rrect) and> the missing value of cc is not replaced.> (Bummer...)> > A...

Re: Replacing variable values with proc sql #7
dassybr@HOTMAIL.COM wrote: >Thanks for your comment! >The problem is that I have a dataset with many variables and it would be >crazy to list them all. So if I do: > >create table test; >select *, case > when cc is missing then 'Missing' >from dataset; >quit; > >I get a warning that cc already exists in the dataset(which is correct) and >the missing value of cc is not replaced. >(Bummer...) > >Any comments on that ;-)? I have one. I recommend that you totally skip this entire process. Really. All you're doing is assigning a new...

Re: Replacing variable values with proc sql #8 1551481
Hadassa, You looking for the coalesce function, incidently you can use it in the data step too. Toby Dunn From: Hadassa Brunschwig <dassybr@HOTMAIL.COM> Reply-To: Hadassa Brunschwig <dassybr@HOTMAIL.COM> To: SAS-L@LISTSERV.UGA.EDU Subject: Replacing variable values with proc sql Date: Wed, 8 Feb 2006 03:16:37 -0500 Hi SAS-L users! I was wondering if there is a possibility to replace values of a variable with proc sql. The data step would look like the following: data test; set test; if var=' ' then var='No Value'; run; The if and then statement i...

Re: Replacing variable values with proc sql #8 660751
A truly quick-and-dirty fix is to switch the SELECT list around so that the replacement column appears first, as in create table test; select case when cc is missing then 'Missing' else cc end as cc, * from dataset; You will still get the warning, but the new CC column will be the one which survives the name collision. On Wed, 8 Feb 2006 04:25:49 -0500, Hadassa Brunschwig <dassybr@HOTMAIL.COM> wrote: >Thanks for your comment! >The problem is that I have a dataset with many variables and it would be >crazy to list them ...

Re: Replacing variable values with proc sql #9 660755
This is one area where I personally see using data step options keep and rename with sql: Proc Sql ; create table test ( drop= _cc ) as select * , Coalesce( _cc , 'Missing') as CC from dataset ( rename = ( cc = _cc ) ) ; quit ; Toby Dunn From: "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM> Reply-To: "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM> To: SAS-L@LISTSERV.UGA.EDU Subject: Re: Replacing variable values with proc sql Date: Wed, 8 Feb 2006 23:06:34 -0500 A truly quick-and-dirty fix is to swit...

Re: Replacing variable values with proc sql #9 1551485
Hadassa: You can use the following: proc sql; Update test set var='No Value' where var is missing; quit; J S Huang 1-515-557-3987 fax 1-515-557-2422 >>> Hadassa Brunschwig <dassybr@HOTMAIL.COM> 2/8/2006 2:16:37 AM >>> Hi SAS-L users! I was wondering if there is a possibility to replace values of a variable with proc sql. The data step would look like the following: data test; set test; if var=' ' then var='No Value'; run; The if and then statement is what I am looking for in proc sql (without creating a macro). Thanks ...

proc sql: variable values into macro variable
Hi, all I have a data set containing a character variable (replay) and want to concatenate all the values into a macro variable. I know this is an easy task for PROC SQL, but can't find the right syntax. I.e., from the dataset: panel plot replay 1 1 1:1 4 2 4:2 2 3 2:3 5 4 5:4 3 5 3:5 6 6 6:6 I want to create a macro variable, say replay, that would be equivalent to %let replay = 1:1 4:2 2:3 5:4 3:5 6:6; best, -Michael -- Michael Friendly Email: friendly@yorku.ca Pro...

Re: proc sql: variable values into macro variable
proc sql noprint; select replay into :replay separated by ' ' from dataset ; quit; %Put &replay; -----Original Message----- From: Michael Friendly [mailto:friendly@yorku.ca] Sent: Thursday, December 11, 2003 5:23 PM To: SAS-L@LISTSERV.UGA.EDU Subject: proc sql: variable values into macro variable Hi, all I have a data set containing a character variable (replay) and want to concatenate all the values into a macro variable. I...

Re: proc sql: variable values into macro variable #4
While it is much simpler to do it in SQL, it is possible in a data step too. This solution is limited by the maximum length possible for a character variable. data _null_ ; set <input data> end = eof ; length t $32767 ; retain t ; t = trim(t) || " " || replay ; if eof then call symput("replay",trim(left(t))) ; run ; _________________________________ Venky Chakravarthy E-mail: swovcc_AT_hotmail_DOT_com -----Original Message----- From: Michael Friendly [mailto:friendly@YORKU.CA] Sent: Thursday, December 11, 2003 5:23 PM To: SAS-L@LISTSERV.UGA.EDU Subje...

Re: proc sql: variable values into macro variable #3 625487
friendly@yorku.ca wrote: > I have a data set containing a character variable (replay) and > want to concatenate all the values into a macro variable. I > know this is an easy task for PROC SQL, but can't find the right > syntax. > . . . . > > I want to create a macro variable, say replay, that would be equivalent > to > > %let replay = 1:1 4:2 2:3 5:4 3:5 6:6; proc sql noprint; select replay into :mreplay separated by ' ' from work.test; quit; HTH, David -- David Cassell, CSC Cassell.David@epa.gov Senior computing specialist mathematical statis...

Re: proc sql: variable values into macro variable #3 1541167
Hi Michael, data test; input panel plot replay $; cards; 1 1 1:1 4 2 4:2 2 3 2:3 5 4 5:4 3 5 3:5 6 6 6:6 ; run; proc sql noprint; select replay into :mvar separated by ' ' from test; quit; %put >&mvar<; Hope this is helpful, Mark Terjeson Reporting, Analysis, and Procurement Section Information Services Division Department of Social and Health Services State of Washington mailto:terjem@dshs.wa.gov -----Original Message----- From: Michael Friendly [mailto:friendly@YORKU.CA] Sent: Thursday, December 11, 2003 2:23 PM To: SAS-L@LISTSERV.UGA.EDU Subje...

Assigning value to a variable in proc sql
Hello all; I am using proc sql to join two datasets in SAS 9.1.3. The two datasets have a common variable 'category'. The variable has the value 'A' in one dataset and 'B' in the other. I would like to retain the variable in the new dataset but assign a new value, say 'C'. Any suggestions as to how this could be done? Kindly note that, I am not using this variable for merging and have other variables which are sufficient to merge the two datasets. Thanks and regards n n, All sort of derived mappings are possible: SELECT tab1.category as category, ... FR...

subsetting to unique values of a *single variable* in proc sql
hi all, My understanding is that 'distinct' in proc sql will subset to unique observations--rows--. How can I subset to the unique values of a single variable regardless of other variables in the same observation? example what I have: var1 var2 1 45% 2 45% 3 45% 4 45% What i want: var1 var2 value doesnt matter 45% (REGARDLESS of what value var1 will take upon subsetting) Thank you very much! Hala Hi Hala, You can use MONOTONIC() to get the desired result. data inp; input v1 v2 $ v3; cards; 1 10% 121 2 10% 111 3 10% 141 4 ...

PROC SQL: Concatenating values of several columns into several macro variables
Hello! I am using a proc sql statement like the one given below, which works fine: proc sql; select sum(z1),sum(z2),sum(z3) into :s1 separated by ' ', :s2 separated by ' ', :s3 separated by ' ' from dataset where event = 1 group by time; quit; What I want is that instead of having to specify for each variable " separated by ' ' ", I would like to have something like select sum(z1),sum(z2),sum(z3) into :s1-:s3 separated by ' ' since in general I have much more than 3 variables. However, that stat...

Way to select list of months value by using macro variable in PROC SQL
I come to following problem: I need to use PROC SQL to extract a list of attributes from a table. These attributes are like: a_200210 a200211...a_200504 In other words, their suffix are named for each month. I can't directly use following syntax in PROC SQL like PROC SQL; select table_name.* from table_name(keep= a_200210-a_200504); Because SAS think a_200213 should follow a_200212. Therefore I have to break them out, like (Keep=a_200210_a_200212 a_200301-a200312 a200401-....) But I need to put this code in a Macro. Therefore my start month can be changed. I want to use a macro varia...

Please check this SQL line for me
In a stored procede, I want to assign the return value of =91SELECT DATEADD(dd, -Day(GetDate()), GetDate())=92 to a variable. The following is what I have currently, but it does not work: SET @PreMonthEnd =3D SELECT DATEADD(dd, -Day(GetDate()), GetDate()) What will be the correct statement? Thanks. Anthony You do not need SELECT: SET @PreMonthEnd = DATEADD(dd, -DAY(CURRENT_TIMESTAMP), CURRENT_TIMESTAMP); -- Plamen Ratchev http://www.SQLStudio.com Thank you so much, Plamen! That should work. (Would it work the same way as the following: SET @PreMonthEnd = (SELECT DATEADD(dd, -Da...

replacing variable values
Hi, I want to replace a variable value in a text file. the text file is cat propertyfile var1="value1" var2="value2" var3="value3" Now, i would like to replace $var1 in "propertyfile" using sed or awk. I want to change propertyfile to cat propertyfile var1="New val1" var2="New val2" var3="New val3" Thanks in advance, an On 2006-04-11, anju wrote: > Hi, > > I want to replace a variable value in a text file. the text file is > > cat propertyfile > var1="value1" > var2="value2" ...

should I keep a variable in proc logistic stepwise regression when its type 3 p-value < 0.001 but chisquare p-value ~ 0.9
Hi, Should I keep a variable in proc logistic stepwise regression when its type 3 p-value < 0.001 but chisquare p-value ~ 0.9? Thanks, Liang Lu ...

Proc SQL and Macro Variables
I have seen instances where the macro processor seems to break up a proc SQL statement at a logical boundary of 255 or 256 depending on how you like to count. In any case if the characters around this boundary are && breaking them apart and then resolving the SQL string does not work because the && does not resolve to &. I have not seen anything in the documentation that suggts this should be a problem but it looks like the Macro Processor is trying to reoslve blocks of code in 256 byte increments. Any insight on this???? ...

proc sql variable creation
hi all, I am creating a variable using proc sql in an existing data set. proc sql; create table new as select x, x + 10 as x1 from old; quit; in OLD data set x has a format and label i want the same to be copied to x1 as well. Is there any option any help would be appreciated. Thank you Hello, Two solutions with SQL and Data Step : *-- Sample dataset --*; proc sql; create table WORK.OLD (x num format=ddmmyy10. label='Hello' ) ; insert into WORK.OLD select monotonic() from SASHELP.CLASS ; quit; *-- SQL solution --*; proc sql; create t...

Macro Variable in proc sql
Guys, I have a question...can we use the macro variable declared outside the scope of proc sql in sql code...because i tried it did not work... lemme give a code so that it will be more clear %let year1=2006; proc sql; create table new as select amount, account from data1 where year=&year1; quit; it gives me error that the apparent symbol year not resolved... declaring the macro variable inside proc sql works but i dont want that as purpose of my program is just to change the value of year everytime n you will get the year specific results... Any suggestions appreciated Thanx Shaunak ...