Desktop productivity for business analysts and programmers

PROC SQL CASE

Reply
Frequent Contributor
Posts: 122

PROC SQL CASE

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

Super User
Super User
Posts: 7,693

Re: PROC SQL CASE

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;

Frequent Contributor
Posts: 122

Re: PROC SQL CASE

Hi

iit worked thank you Smiley Happy

Super User
Posts: 19,039

Re: PROC SQL CASE

Please mark the question answered.

Super User
Posts: 11,107

Re: PROC SQL CASE

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

Ask a Question
Discussion stats
  • 4 replies
  • 345 views
  • 2 likes
  • 4 in conversation