Hierarchical Merge

[SAShole]

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;

[ballardw]

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.

[SAShole]

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

[ballardw]

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.

[AhmedAl_Attar]

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

[Vish33]

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