BookmarkSubscribeRSS Feed
marleeakerson
Calcite | Level 5

Hello,

 

I am trying to generate a zipcode from the SASHELP.ZIPCODE table that comes with SAS using a county I have in my table. 

 

I would like to take the county I have in my table and generate a new variable, called zip_code, that is the last zip code listed for that county (in the SASHELP.ZIPCODE file). 

 

Could someone help with the coding of this? 

 

Thank you!

10 REPLIES 10
PeterClemmensen
Tourmaline | Level 20

show us your data.

marleeakerson
Calcite | Level 5

Here is an example of the data: 

 

ID            County           State

1               Denver           CO 

2              Jefferson         CO

3              Rifle                 CO

 

And I want it to look something like this:

 

ID            County           State            Zip Code

1               Denver           CO               00010

2              Jefferson         CO               00020

3              Rifle                 CO              00030

 

Where that zip code is pulled from the SASHELP.ZIPCODE table, and it is the last zip code for the county listed (so for example - zip code 00010 would be the last zip code listed under the Denver County zip codes in the SASHELP.ZIPCODE table)

 

 

CurtisMackWSIPP
Lapis Lazuli | Level 10
He did, its SASHELP.ZIPCODES
CurtisMackWSIPP
Lapis Lazuli | Level 10

I'm not sure what you mean by the "last" zip, but something like this?

 

proc sort data=sashelp.zipcode out=zips;
  by state county;
run; 

data lastzip;
  set zips;
  by state county;
  if last.county;
run;
marleeakerson
Calcite | Level 5

By last zip code, I mean that for every county in the SASHELP.ZIPCODE table, there are going to be multiple zip codes listed (for example - for Denver county, there might be 10 zip codes from 00000 to 00010) and I want whatever the last zip code for that county to be reflected in the table. 

 

I think using the last. function is correct I am just not sure exactly how to use it. 

CurtisMackWSIPP
Lapis Lazuli | Level 10

Did my example not solve it?

CurtisMackWSIPP
Lapis Lazuli | Level 10

If you really just want the last one as it is sorted (or not as in this case) you can use the nosorted option.

 

data lastzip;
  set sashelp.zipcode;
  by state county notsorted;
  if last.county;
run;

marleeakerson
Calcite | Level 5

It did not totally fix the issue because this what the data I have already looks like: 

 

(my current table - not the SASHELP.ZIPCODE table)

ID            County           State

1               Denver           CO 

2              Jefferson         CO

3              Rifle                 CO

 

And I want to add a zip code variable to my table, and I want that zip code to be the last zip code listed in the SASHELP.ZIPCODE table per the county it is in, so the final table will looks something like this. I imagine I will need to merge somehow using the last. function. 

 

(my new table - with the zipcode pulled from the SASHELP table)

ID            County           State            Zip Code

1               Denver           CO               00010

2              Jefferson         CO               00020

3              Rifle                 CO              00030

 

 

CurtisMackWSIPP
Lapis Lazuli | Level 10

Its just a merge after that.  The only trick is getting the matching variables.

 

proc sort data=sashelp.zipcode(where = ( statecode='CO')) out=zips;
  by statecode COUNTYNM;
run; 

data lastzip;
  set zips;
  by statecode COUNTYNM notsorted;
  if last.COUNTYNM;
run;

data mydata;
  length COUNTYNM $ 30 statecode $ 2;
  infile datalines dsd;
  input ID COUNTYNM $ statecode $;
datalines;
1,Denver,CO 
2,Jefferson,CO
3,Rifle,CO
;
run;
proc sort data=mydata;
  by statecode COUNTYNM;
run; 

data mymerged;
  merge mydata(in = wanted)
        lastzip(keep = statecode COUNTYNM zip);
  by statecode COUNTYNM;
  if wanted;
run; 

But note that Rifle county is not in SASHELP.ZIPCODES.

art297
Opal | Level 21

I'm not sure how the zipcode file is actually sorted, so I added a recnum to each record and used it to eliminate all but the last record for a given country within a state.

 

However, the following will only work as intended if your state and country abbreviations/names are the same as those in the sashelp.zipcode file. That won't be the case for Rifle, CO as there is no such country in the sashelp.zipcode file.

data have;
  informat County $32. State $2.;
  input ID  County State;
  cards;
1  Denver     CO 
2  Jefferson  CO
3  Rifle      CO
;
run;

data county_zipcode;
  set sashelp.zipcode (keep=zip statecode countynm
                       rename=(statecode=state
                               countynm=county
                               zip=zip_code));
  recnum=_n_;
run;
  
proc sort data=county_zipcode;
  by state county descending recnum;
run;

proc sort data=county_zipcode nodupkey;
  by state county;
run;

data want;
  merge have(in=ina) county_zipcode (drop=recnum);
  by state county;
  if ina;
run;

Art

 

 

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 10 replies
  • 1161 views
  • 0 likes
  • 4 in conversation