BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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;
3 REPLIES 3
Olivier
Pyrite | Level 9
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
Not applicable
Many thanks... it is starting to come back to me and now I am on to more challenging recollections.
mftuchman
Quartz | Level 8
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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 564 views
  • 0 likes
  • 3 in conversation