How to simplify long case when coding with loop

[elisazhsn]

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!

[Patrick]

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

[elisazhsn]

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?

[Patrick]

---

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