How to create variables in PROC SQL?

[afiqcjohari]

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?

[Reeza]

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#n1spf244gkq54cn13...

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

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

[afiqcjohari]

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

[art297 · Accepted solution]

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

[afiqcjohari]

Ok, I missed the length option. Thanks!

[RW9]

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;

[afiqcjohari]

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