Hi All,
I have to make condition always true irrespective of any value in character variable, I know I could do that for numeric variable like 1=1 but how to do that for character variable is something I am having problems with right now...
So I have following code
Proc sql;
select var1,var2,
case
var3 when ("_ALL_" ,'1=1')
then case
when var4>10 then 'y'
else 'n' end
else 'n' end as flag
from have;
quit;
Here, "_ALL_" is coming from a macro variable named mac1, however in few cases (like the one mentioned above)I need to get the next case statement executed irrespective of any value.... I used '1'='1' but it doesnt work.. Any ideas...???
Thanks in adavance
You need to change the format of your CASE statement to move the variable reference inside the WHEN condition.
Check out the manul page on the CASE statement.
http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a002473682.htm
Example
The following two PROC SQL steps show two equivalent CASE expressions that create a character column with the strings in the THEN clause. The CASE expression in the second PROC SQL step is a shorthand method that is useful when all the comparisons are with the same column.
proc sql;
select Name, case
when Continent = 'North America' then 'Continental U.S.'
when Continent = 'Oceania' then 'Pacific Islands'
else 'None'
end as Region
from states;
proc sql;
select Name, case Continent
when 'North America' then 'Continental U.S.'
when 'Oceania' then 'Pacific Islands'
else 'None'
end as Region
from states;
Note: When you use the shorthand method, the conditions must all be equality tests. That is, they cannot use comparison operators or other types of operators.
Since you want any character value if present to be true
you can try the below code
Proc sql;
select var1,var2,
case when var3 ne ' '
then case
when var4>10 then 'y'
else 'n' end
else 'n' end as flag
from have;
quit;
Hope this works
Thanks,
Jagadish
Hi Jagadish, it would work but I want to select only for particular values not for all.. so it's more like in statement.
I am looking for something like
when var3 in ('_ALL_','Some value here which will be true only for few observations')
Is it possible, or do i need to write another case for that..??
Is it possible for you to provide the sample data and the output you wish to get to provide better answer.
My input data is like below :
var1 | var2 | var3 | var4 |
a | p | pen | 16 |
a | p | _all_ | 3 |
b | a | pen | 13 |
b | c | pencil | 7 |
d | e | eraser | 9 |
e | r | sharpner | 34 |
And I want output as below :
var1 | var2 | var3 | var4 | flag |
a | p | pen | 16 | y |
a | p | _all_ | 3 | y |
b | a | pen | 13 | y |
b | c | pencil | 7 | n |
d | e | eraser | 9 | n |
e | r | sharpner | 34 | y |
If you look at 2nd observation, its value is less than 10 still its flagged as y , because _all_ here means all elements in that group and whenever any one of them satisfies condition all should be flagged as y , for rest of the observations thats not the case...
Use the logic version of the case expression :
Proc sql;
select var1,var2,
case
when upcase(var3)="_ALL_" or var4>10
then "y"
else "n"
end as flag
from have;
quit;
PG
PROC SQL;
SELECT NAME,SEX, AGE,
CASE WHEN AGE IN (10,11,12)
THEN CASE
WHEN HEIGHT>10 THEN 'Y'
ELSE 'N' END
ELSE 'N' END AS FLAG
FROM SASHELP.CLASS;
QUIT;
You need to change the format of your CASE statement to move the variable reference inside the WHEN condition.
Check out the manul page on the CASE statement.
http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a002473682.htm
Example
The following two PROC SQL steps show two equivalent CASE expressions that create a character column with the strings in the THEN clause. The CASE expression in the second PROC SQL step is a shorthand method that is useful when all the comparisons are with the same column.
proc sql;
select Name, case
when Continent = 'North America' then 'Continental U.S.'
when Continent = 'Oceania' then 'Pacific Islands'
else 'None'
end as Region
from states;
proc sql;
select Name, case Continent
when 'North America' then 'Continental U.S.'
when 'Oceania' then 'Pacific Islands'
else 'None'
end as Region
from states;
Note: When you use the shorthand method, the conditions must all be equality tests. That is, they cannot use comparison operators or other types of operators.
Thanks guys.. it worked...
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.