Change value, if in ("AZ", "CA", ...) then "US"

[deleted_user]

I have a mixed state and country field and am trying to change all the individual US states to "US" so only countries will remain.

My SAS skills are rusty and I've tried data step, SQL, case statements without luck. I am sure I am missing just a simple component/making a simple error. I would greatly appreciate any help. Abridged (non-working) code below.
Thanks in advance!



data RandR;
if ST_COUNTRY_CD in ('AZ', 'NJ') then ORIGIN = "US";
else ORIGIN=ST_COUNTRY_CD;
run;

proc sql;
create table RandR as
select Renew.ST_COUNTRY_CD, Renew.ST_COUNTRY_CD as ORIGIN
case
when ST_COUNTRY_CD in ('AZ', 'NJ') then 'US'
end as ORIGIN
from Ram, Renew
quit;

[Olivier]

Hi Melissa.

1) The DATA step : there is a SET statement missing -- you don't read any data !
2) The SQL query : the is no condition for merging properly the two tables (usually it's set in a WHERE clause) ; plus the correct CASE would be
[pre]
case
when ST_COUNTRY_CD in ('AZ', 'NJ') then 'US'
else Renew.ST_COUNTRY_CD
end as ORIGIN
[/pre]
Regards.
Olivier

[deleted_user]

Many thanks... it is starting to come back to me and now I am on to more challenging recollections.

[mftuchman]

Another approach is to use the FORMAT procedure.

The code may look something like this:

PROC FORMAT;
VALUE $SC
'AK','AL','AZ','CA','CO','CT','DC','DE',
'FL','GA','HI','IA','ID','IL','IN','KS','KY','LA',
'MA','MD','ME','MI','MN','MO','MS','MT',
'NC','ND','NE','NH','NJ','NM','NV','NY',
'OH','OK','OR','PA','RI','SC','SD','TN',
'TX','UT','VA','VT','WA','WI','WV','WY' = 'USA'
other= 'NON-US';
run;

Then your code could read

IF PUT(STATE,$SC.) = "USA" then do;
/* put US-based code here */
end;

else do;
/* put foreign code here */
end;

Just to double check - if you want to handle GUam, Puerto Rico, and the US Virgin Islands as US, just add it to the format.