topic Re: PROC SQL CASE in SAS Enterprise Guide

PROC SQL CASE

chennupriya — Wed, 11 Feb 2015 15:34:50 GMT

hi I hav a dataset which has both numericals and character . When numeric ala I want to change to character but when character leave as it is . When 1 I want to be replaced with FL when it is AZ leave as it is in STate variable

ssample dataset

ddata test1;

input code zipcode;

1 78240

2 23239

3 45670

AL 34567

AZ 43678

;

run;

proc sql;

create te table xy as

select code ,

pincode,

case code

when 1 then 'FL'

when 2 then 'CA'

when 3 then 'SC'

else code

end as State;

from test1;

quit;

but but this syntax is error

Re: PROC SQL CASE

RW9 — Wed, 11 Feb 2015 15:50:56 GMT

Yes, the reason is in your sample dataset, SAS automatically assigns the first column to be a character variable as you have non-numeric data in it. Hence later on when you try to do a case on numeric data then you get an error as the variable is character. Just change the case to look at characters (also correct some typos in your code):

proc sql;

create table xy as

select code,

pincode,

case code when "1" then 'FL'

when "2" then 'CA'

when "3" then 'SC'

else code end as State

from test1;

quit;

Re: PROC SQL CASE

chennupriya — Wed, 11 Feb 2015 16:36:32 GMT

iit worked thank you

Re: PROC SQL CASE

Reeza — Wed, 11 Feb 2015 16:48:55 GMT

Please mark the question answered.

Re: PROC SQL CASE

ballardw — Wed, 11 Feb 2015 17:13:51 GMT

You may also find the function ZIPSTATE useful. If your Zip code is accurate then State=zipstate(zipcode).

You could just use: select zipstate(zipcode) as state