topic How to simplify long case when coding with loop in SAS Programming

How to simplify long case when coding with loop

elisazhsn — Thu, 30 May 2019 07:00:13 GMT

Hi all,

I'm in a situation where I have to write many "case when" statements to create each of the many new variables in proc sql, while those "case when" statements are of a highly recognized pattern: when the variable starts with A then "A000", when it starts with B then "B000", etc. and it goes all the way to "Z000". I would have to write 26 "case when" statements to create one new variable. I think there must be a way to simplify and shorten the codes.

Here's my original codes:

proc sql;

select var1, var2

  , case when substr(upper(var1),1,1) = 'A' then 'A000'

             when substr(upper(var1),1,1) = 'B' then 'B000'

             when substr(upper(var1),1,1) = 'C' then 'C000'

             when substr(upper(var1),1,1) = 'D' then 'D000'

...

...

...

             when substr(upper(var1),1,1) = 'Z' then 'Z000'

   end as var1_new

from table;

quit;

Hope there's a way to save me from this exhausting coding. Any help would be very much appreciated!

Re: How to simplify long case when coding with loop

Patrick — Thu, 30 May 2019 07:19:07 GMT

Not tested but something like below should cover all letters at once.

 case when findc(substr(var1,1,1),,'LU')=1 then substr(upcase(var1),1,1)||'000'
 else <?>
 end as var1_new length=$<?>

https://go.documentation.sas.com/?docsetId=lefunctionsref&docsetTarget=n1mdh2gvd5potjn14jipysvzn4o7.htm&docsetVersion=9.4&locale=en

Re: How to simplify long case when coding with loop

elisazhsn — Thu, 30 May 2019 08:15:07 GMT

Thank you a lot for your quick reply!

I don't get 'LU' in your findc() function, what does it mean? Also, does <?> mean fuzzy search here?

Re: How to simplify long case when coding with loop

Patrick — Thu, 30 May 2019 08:31:13 GMT

@elisazhsn wrote:

Thank you a lot for your quick reply!

I don't get 'LU' in your findc() function, what does it mean? Also, does <?> mean fuzzy search here?

'LU' stand for Lowercase letters and Uppercase letters. The link I've posted will take you to the docu page for findc() where you can get the details.

<?> means that this is stuff you have replace with what's suitable for your case. I don't know what you want to do in the ELSE case and I also can't know how long the resulting variable needs to be.