I have datasets a & b as following. How do I get dataset c ?
dataset a
Prg_ID | State |
1004 | AL |
1005 | AL |
1007 | MI |
1008 | MI |
1009 | MI |
dataset b
State | ZipCode |
AL | 35004 |
AL | 35005 |
AL | 35006 |
AL | 35007 |
AL | 35010 |
AL | 35011 |
AL | 35013 |
MI | 48871 |
MI | 48872 |
MI | 48873 |
dataset c
Prg_ID | State | ZipCode |
1004 | AL | 35004 |
1004 | AL | 35005 |
1004 | AL | 35006 |
1004 | AL | 35007 |
1004 | AL | 35010 |
1004 | AL | 35011 |
1004 | AL | 35013 |
1005 | AL | 35004 |
1005 | AL | 35005 |
1005 | AL | 35006 |
1005 | AL | 35007 |
1005 | AL | 35010 |
1005 | AL | 35011 |
1005 | AL | 35013 |
1007 | MI | 48871 |
1007 | MI | 48872 |
1007 | MI | 48873 |
1008 | MI | 48871 |
1008 | MI | 48872 |
1008 | MI | 48873 |
1009 | MI | 48871 |
1009 | MI | 48872 |
1009 | MI | 48873 |
When you want a many-to-many match, the best tool for the job is usually SQL. For example:
proc sql;
create table c as
select a.*, b.zipcode
where a.state=b.state;
run;
EDIT: I misunderstood the question, so this isn't relevant but may be helpful 🙂
What version of SAS are you using?
Do you want to use Dataset B for sure?
If this is homework, then probably. If not, SAS provides several datasets and functions for working with Zipcodes and spatial US data.
In 9.3+ there are several functions available here:
State and ZIP code | FIPNAME Function | Converts two-digit FIPS codes to uppercase state names. |
---|---|---|
FIPNAMEL Function | Converts two-digit FIPS codes to mixed case state names. | |
FIPSTATE Function | Converts two-digit FIPS codes to two-character state postal codes. | |
STFIPS Function | Converts state postal codes to FIPS state codes. | |
STNAME Function | Converts state postal codes to uppercase state names. | |
STNAMEL Function | Converts state postal codes to mixed case state names. | |
ZIPCITY Function | Returns a city name and the two-character postal code that corresponds to a ZIP code. | |
ZIPCITYDISTANCE Function | Returns the geodetic distance between two ZIP code locations. | |
ZIPFIPS Function | Converts ZIP codes to two-digit FIPS codes. | |
ZIPNAME Function | Converts ZIP codes to uppercase state names. | |
ZIPNAMEL Function | Converts ZIP codes to mixed case state names. | |
ZIPSTATE Function | Converts ZIP codes to two-character state postal codes. |
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.