topic Re: Proc sql question : conditon always true in SAS Programming

Proc sql question : conditon always true

forumsguy — Sat, 27 Jul 2013 07:17:22 GMT

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

Re: Proc sql question : conditon always true

Jagadishkatam — Sat, 27 Jul 2013 07:32:32 GMT

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

Re: Proc sql question : conditon always true

forumsguy — Sat, 27 Jul 2013 08:00:55 GMT

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

Re: Proc sql question : conditon always true

Scott_Mitchell — Sat, 27 Jul 2013 08:25:24 GMT

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;

Re: Proc sql question : conditon always true

Jagadishkatam — Sat, 27 Jul 2013 08:26:14 GMT

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

Re: Proc sql question : conditon always true

forumsguy — Sun, 28 Jul 2013 01:26:11 GMT

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

Re: Proc sql question : conditon always true

Tom — Sun, 28 Jul 2013 01:50:39 GMT

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.

Re: Proc sql question : conditon always true

PGStats — Sun, 28 Jul 2013 01:52:32 GMT

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;

Re: Proc sql question : conditon always true

forumsguy — Mon, 29 Jul 2013 05:24:38 GMT

Thanks guys.. it worked...