BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
forumsguy
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

8 REPLIES 8
Jagadishkatam
Amethyst | Level 16

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
forumsguy
Fluorite | Level 6

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

Jagadishkatam
Amethyst | Level 16

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

Thanks,
Jag
forumsguy
Fluorite | Level 6

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

PGStats
Opal | Level 21

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
Scott_Mitchell
Quartz | Level 8

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;

Tom
Super User Tom
Super User

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.

forumsguy
Fluorite | Level 6

Thanks guys.. it worked...

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!

How to Concatenate Values

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.

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
  • 8 replies
  • 2097 views
  • 4 likes
  • 5 in conversation