Hi all,
suppose I've got a code like this:
proc sql;
create table DATASET1 as select
case when a.VAR1 + a.VAR2 > 3 then 'UVAR1'
end as MYVAR1,
case when a.VAR1 + a.VAR3 = 0 then 'UVAR2'
end as MYVAR2
from DATASET;
quit;
I would like to add an extra 'case when', but instead of using variables from DATASET I would like to use my variables MYVAR1 and MYVAR2. Is there a way to do that?
thanks
Actually, there is.
Use the keyword Calculated, which an extremely useful SAS extension to ANSI SQL. It's more cumbersome to solve this in other SQL dialects.
Actually, there is.
Use the keyword Calculated, which an extremely useful SAS extension to ANSI SQL. It's more cumbersome to solve this in other SQL dialects.
Hi,
As LinusH mentioned there is calculated. However you can also do these things by subquerying. There are times when each method is useful, depending on the complexity of the SQL being created. The below is logically the same, the first using a value which is calculated within the same select, the second having that value created as part of the select in the subquery. In this simple example it may look a bit pointless, however if you are writing large queries which utilize many datasets, calculations, grouping etc. (say something like and ADSL table) then it can be useful to pre-process data going into your query to simplify things.
proc sql;
create table ... as
select values,
case when else,
case calculated when else
from a_dataset;
quit;
proc sql;
create table ... as
select values,
case when calculated else
from (select values,
case when else as calculated
from a_dateset);
quit;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.