Hi SAS friends,
I was wondering if it is possible to merge on a higher level variable simultaneously. for example to merge on state and county in one step (all state codes are built into county codes).
data County_Pop;
input State :$2. County :$5. cName :$25. Population;
format Population Comma16.;
datalines;
01 01001 Autauga 222135
01 01011 Bullock 300000
01 01023 Choctaw 115000
42 42003 Allegheny 389471
42 42101 Philadelphia 2689765
;;;;
run;
data County_HH;
input State :$2. County :$5. cName :$25. Households;
format Households Comma16.;
datalines;
01 01001 Autauga 106625
01 01011 Bullock 144000
01 01023 Choctaw 55200
42 42003 Allegheny 186946
42 42101 Philadelphia 1291087
run;
data State_Names;
input State :$2. sName $25.;
datalines;
01 Alabama
42 Pennsylvania
run;
data want;
merge County_HH County_Pop State_Names;
by ??? ;
run;
Since both of your County_Hh and County_Pop data sets contain the "county", which I recognize as FIPS or whatever they changed the name to codes, and a State code I would instead use the SAS supplied FIPSTATE or FIPNAMEL function to create the desired text instead of bothering to merge a third dataset.
Add code:
sName = FIPNAMEL(state);
to the data set merging the two sets.
Thanks ballardw. i never knew about the FIPNAMEL function. Do you know if there is a similar function for Census Region/Division?
No function I am aware of. However if you can get any list of code values with text you want to display it is not very difficult to create a custom format that will display the desired value OR use a Put statement to create a new variable. Look up Proc Format and a CNTLIN dataset.
Hash tables and/or sql joins are also another way to merge data if you can get the codes and values in a dataset.
I developed the attached SAS macro (util_addCensusRegnDiv.sas) sometime ago, and here is a usage example
/* ***************************************************** */
/* Add Census Regions and Divisions of the United States */
/* ***************************************************** */
%util_addCensusRegnDiv( p_dsName=county_HH, p_stateVarName=%str(input(state,$2.)) );
Hope this helps,
Ahmed
Hi ,
I am not aware of this functions mentioned above by ballardw..i did normal merge on your data to map the state names and codes...
proc sort data=County_Pop;by State County cName; run;
proc sort data=County_HH;by State County cName; run;
proc sort data=State_Names;by State; run;
data want ;
merge County_Pop(in=a) County_HH(in=b) State_Names (in=c);
by State ;
if a and b;
run;
By running the above code i got the o/p as follows
State | County | cName | Population | Households | sName |
01 | 01001 | Autauga | 222,135 | 106,625 | Alabama |
01 | 01011 | Bullock | 300,000 | 144,000 | Alabama |
01 | 01023 | Choctaw | 115,000 | 55,200 | Alabama |
42 | 42003 | Allegheny | 389,471 | 186,946 | Pennsylvania |
42 | 42101 | Philadelphia | 2,689,765 | 1,291,087 | Pennsylvania |
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.