DATA Step, Macro, Functions and more

Proc sql question : conditon always true

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 85
Accepted Solution

Proc sql question : conditon always true

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


Accepted Solutions
Solution
‎07-27-2013 09:50 PM
Super User
Super User
Posts: 6,497

Re: Proc sql question : conditon always true

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.

View solution in original post


All Replies
Trusted Advisor
Posts: 1,128

Re: Proc sql question : conditon always true

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



Thanks,
Jag
Frequent Contributor
Posts: 85

Re: Proc sql question : conditon always true

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..??

Trusted Advisor
Posts: 1,128

Re: Proc sql question : conditon always true

Is it possible for you to provide the sample data and the output you wish to get to provide better answer.

Thanks,
Jag
Frequent Contributor
Posts: 85

Re: Proc sql question : conditon always true

My input data is like below :

var1var2var3var4
appen16
ap_all_3
bapen13
bcpencil7
deeraser9
ersharpner34

And I want output as below :

var1var2var3var4flag
appen16y
ap_all_3y
bapen13y
bcpencil7n
deeraser9n
ersharpner34y

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...

Respected Advisor
Posts: 4,641

Re: Proc sql question : conditon always true

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

PG
Super Contributor
Posts: 297

Re: Proc sql question : conditon always true

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;

Solution
‎07-27-2013 09:50 PM
Super User
Super User
Posts: 6,497

Re: Proc sql question : conditon always true

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.

Frequent Contributor
Posts: 85

Re: Proc sql question : conditon always true

Thanks guys.. it worked...

☑ This topic is SOLVED.

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

Discussion stats
  • 8 replies
  • 546 views
  • 4 likes
  • 5 in conversation