f



Replacing variable values with proc sql

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
dassybr (6)
2/8/2006 8:16:37 AM
comp.soft-sys.sas 142828 articles. 2 followers. Post Follow

4 Replies
2518 Views

Similar Articles

[PageSpeed] 58

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
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
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
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
tobydunn (6020)
2/8/2006 2:29:38 PM
Reply: