topic Re: Multiple variables with CASE in Proc SQL in SAS Procedures

Multiple variables with CASE in Proc SQL

VD — Fri, 27 Aug 2010 09:54:19 GMT

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.

Re: Multiple variables with CASE in Proc SQL

Florent — Fri, 27 Aug 2010 10:48:53 GMT

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

Re: Multiple variables with CASE in Proc SQL

Peter_C — Fri, 27 Aug 2010 11:48:26 GMT

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

Re: Multiple variables with CASE in Proc SQL

VD — Fri, 27 Aug 2010 15:03:37 GMT

Many thanks to both of you.