Help using SASHELP.ZIPCODE

[marleeakerson]

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!

[PeterClemmensen]

show us your data.

[marleeakerson]

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]

He did, its SASHELP.ZIPCODES

[CurtisMackWSIPP]

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]

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]

Did my example not solve it?

[CurtisMackWSIPP]

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]

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]

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]

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