BookmarkSubscribeRSS Feed
VD
Calcite | Level 5 VD
Calcite | Level 5
Is it possible to create more than variable using case in proc sql.

For creating one variable, the code (which works fine) is:
case
when DaysDiff<=10 then '0-10'
when 11<=DaysDiff<=20 then '11-20'
when 21<=DaysDiff<=30 then '21-30'
when 31<=DaysDiff<=40 then '31-40'
else 'Greater than 40'
end as NumberOfDays

I'm trying to create two variables, with something like this:
case
when DaysDiff<=10 then '0-10' and '1'
when 11<=DaysDiff<=20 then '11-20' and '2'
when 21<=DaysDiff<=30 then '21-30' and '3'
when 31<=DaysDiff<=40 then '31-40' and '4'
else 'Greater than 40' and '5'
end as NumberOfDays and NumberOfDaysSequence

The code (and its different variants which I've tried) doesn't work. Is there any way two variables can be created with Case?
Thanks.
3 REPLIES 3
Florent
Quartz | Level 8
Hi,

I'm not sure if I understand correctly the meaning of your question but I'll try to answer it.

Maybe could you combine your Proc SQL with the macro facility ?

That way you are able to conditionally add variables to a table:

Proc sql;
Create Table MyNewTable as
select a.FirstVariable
%IF (First condition) %THEN %DO;
, 'ConditionIsTrue' as SecondVariable
%END; %ELSE %DO;
, 'ConditionIsFalse' as ThirdVariable
%END;
, 'DummyValue' as LastVariable
from MyOldTable a;
Quit;


The problem is that this solution is not directly based on the value of other variables of your base table (excepted if you retrieve those values in previous steps by the mean of macro variable i.e...).

I hope it helps.

Regards,
Florent
Peter_C
Rhodochrosite | Level 12
VD

simply "no".
A case construct has one "output", a bit like a macro which generates text, if the text contains no semicolons then it can be invoked many times within a base SAS statement.
The "output" of a case construct need not be a column in the output, but it cannot be more than one column, unless you assign it to a column to which you can refer more than once.
An example of where a case construct does not become a column is where that "output" is used in deriving another column as in:
select name, case when age gt 12 then 'teen' else 'pre-teen'
end !! 'ager' length=15 as life_stage , age
from sashelp.class

I expect you'll find examples more relevant to your situation

I'm not sure, but expect that a case construct could return a value you could use in a where or having clause,
Probably reading the documentation would clarify.

luck
peterC
VD
Calcite | Level 5 VD
Calcite | Level 5
Many thanks to both of you.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 25643 views
  • 1 like
  • 3 in conversation