topic Re: How to create variables in PROC SQL? in SAS Programming

How to create variables in PROC SQL?

afiqcjohari — Tue, 14 Mar 2017 04:04:53 GMT

in data step

The following will create a new variable newvar based on myvar and drop myvar

data newtable(drop = myvar);
   length newvar %5;
   set table;
   if myvar = N then newvar = No
   if myvar = Y then newvar = Yes
run;

How can I replicate this in SAS proc sql?

Re: How to create variables in PROC SQL?

Reeza — Tue, 14 Mar 2017 04:28:48 GMT

That's not valid SAS code to start with...

As always, see the documentation or search at lexjansen.

https://support.sas.com/documentation/cdl/en/sqlproc/69822/HTML/default/viewer.htm#n1spf244gkq54cn13bbiy307hknr.htm

http://www.lexjansen.com/search/searchresults.php?q=Intro%20to%20proc%20sql

http://www2.sas.com/proceedings/sugi29/268-29.pdf

Re: How to create variables in PROC SQL?

art297 — Tue, 14 Mar 2017 04:31:07 GMT

proc sql;
  create table newtable as
    select case(myvar)
      when ('N') then 'No'
      when ('Y') then 'Yes'
     end as newvar length=5
       from table
  ;
  quit;

Art, CEO, AnalystFinder.com

Re: How to create variables in PROC SQL?

afiqcjohari — Tue, 14 Mar 2017 04:32:32 GMT

proc sql;
create table test as
select case
when myvar= 'N' then 'LCY'
when myvar = 'Y' then 'FCY'
end as newvar
from table
;quit;

Re: How to create variables in PROC SQL?

afiqcjohari — Tue, 14 Mar 2017 04:33:45 GMT

Ok, I missed the length option. Thanks!

Re: How to create variables in PROC SQL?

afiqcjohari — Tue, 14 Mar 2017 04:34:14 GMT

That lexjansen is going to be very useful. Thanks Reeza!

Re: How to create variables in PROC SQL?

RW9 — Tue, 14 Mar 2017 09:31:31 GMT

Hi,

Just to add, when doing binary choices such as this its useful to use ifn/ifc functions to shrink the code (note this would not work for pass-through):

data newtable;
  set table;
  newvar=ifc(myvar="N","No","Yes");
run;

proc sql;
  create table NEWTABLE as
  select  ifc(MYVAR="N","No","Yes") as NEWVAR
  from    TABLE;
quit;