PROC SQL CASE

[chennupriya]

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

[RW9]

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;

[chennupriya]

Hi

iit worked thank you

[Reeza]

Please mark the question answered.

[ballardw]

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