topic Re: Help using SASHELP.ZIPCODE in New SAS User

Help using SASHELP.ZIPCODE

marleeakerson — Tue, 22 Sep 2020 18:12:57 GMT

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!

Re: Help using SASHELP.ZIPCODE

PeterClemmensen — Tue, 22 Sep 2020 18:18:59 GMT

show us your data.

Re: Help using SASHELP.ZIPCODE

marleeakerson — Tue, 22 Sep 2020 18:27:20 GMT

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)

Re: Help using SASHELP.ZIPCODE

CurtisMackWSIPP — Tue, 22 Sep 2020 19:05:58 GMT

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;

Re: Help using SASHELP.ZIPCODE

CurtisMackWSIPP — Tue, 22 Sep 2020 19:07:57 GMT

He did, its SASHELP.ZIPCODES

Re: Help using SASHELP.ZIPCODE

marleeakerson — Tue, 22 Sep 2020 19:09:31 GMT

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.

Re: Help using SASHELP.ZIPCODE

CurtisMackWSIPP — Tue, 22 Sep 2020 19:10:42 GMT

Did my example not solve it?

Re: Help using SASHELP.ZIPCODE

CurtisMackWSIPP — Tue, 22 Sep 2020 19:13:07 GMT

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;

Re: Help using SASHELP.ZIPCODE

marleeakerson — Tue, 22 Sep 2020 19:19:13 GMT

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

Re: Help using SASHELP.ZIPCODE

art297 — Tue, 22 Sep 2020 19:37:37 GMT

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

Re: Help using SASHELP.ZIPCODE

CurtisMackWSIPP — Tue, 22 Sep 2020 19:50:23 GMT

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.