DATA Step, Macro, Functions and more

How to create variables in PROC SQL?

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 99
Accepted Solution

How to create variables in PROC SQL?

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?

 


Accepted Solutions
Solution
‎03-14-2017 12:33 AM
PROC Star
Posts: 7,366

Re: How to create variables in PROC SQL?

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

 

View solution in original post


All Replies
Super User
Posts: 17,952

Re: How to create variables in PROC SQL?

Frequent Contributor
Posts: 99

Re: How to create variables in PROC SQL?

That lexjansen is going to be very useful. Thanks Reeza!
Solution
‎03-14-2017 12:33 AM
PROC Star
Posts: 7,366

Re: How to create variables in PROC SQL?

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

 

Frequent Contributor
Posts: 99

Re: How to create variables in PROC SQL?

Ok, I missed the length option. Thanks!
Super User
Super User
Posts: 7,430

Re: How to create variables in PROC SQL?

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;
Frequent Contributor
Posts: 99

Re: How to create variables in PROC SQL?

proc sql;
create table test as
select case
when myvar= 'N' then 'LCY'
when myvar = 'Y' then 'FCY'
end as newvar
from table
;quit;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 260 views
  • 4 likes
  • 4 in conversation