DATA Step, Macro, Functions and more

Hierarchical Merge

Reply
Contributor
Posts: 62

Hierarchical Merge

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;

Super User
Posts: 11,343

Re: Hierarchical Merge

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.

Contributor
Posts: 62

Re: Hierarchical Merge

Thanks ballardw. i never knew about the FIPNAMEL function. Do you know if there is a similar function for Census Region/Division?

Super User
Posts: 11,343

Re: Hierarchical Merge

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.

Regular Contributor
Posts: 217

Re: Hierarchical Merge

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

Attachment
Frequent Contributor
Posts: 117

Re: Hierarchical Merge

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

StateCountycNamePopulationHouseholdssName
0101001Autauga222,135106,625Alabama
0101011Bullock300,000144,000Alabama
0101023Choctaw115,00055,200Alabama
4242003Allegheny389,471186,946Pennsylvania
4242101Philadelphia2,689,7651,291,087Pennsylvania
Ask a Question
Discussion stats
  • 5 replies
  • 258 views
  • 4 likes
  • 4 in conversation