Search Data Services
Meta Search
search across all the following databases:
Data Catalog
Data and documentation
KnowledgeBase
Common questions and answers.
Resources
Entire collection of data resources.
Latest Data News
Lessons from North of the Border
Nerd Alert: Dictionary of Numbers
Rectangularizing data
Frequently a user needs to combine records from one level of the data file with another level. For instance, in a hierchical file that has housing records and person records:
H00123645 P0155111678 P0253221478 H00223551 P0139111364 P0237221455 P0304139999
One may want to put the housing and person information onto the same record so that one could examine earnings differences (information from the person record) according to residence (information from the housing record).
In the above example, the first column indicates record type (H for housing, P for person). Combining selected information from the housing record (columns 2-5) and the person record (columns 2-6) results in the following:
0012301551 0012302532 0022301391 0022302372 0022304041
The following example shows how to transform the 1990 PUMs data into a rectangular file. The housing record is identified by an 'H' in column 1 and the person record is identified by a 'P' in column 1.
*****************************************************************
filename pums90 pipe 'zcat
/usr/data/public/us/government/pums/1990/1-1000/1990
.Z';
filename out1 'exer1.dat';
/* INPUT data */
data a;
retain state msapmsa;
infile pums90;
input @1 type $1. @;
if type='H' then do;
input state $11-12 msapmsa $20-23;
return;
end;
else do;
input sex 11 race 12-14 age 15-16 marital 17 pwgt1 18-21
rpob 29-30 yearsch 51-52 rlabor 91 rearning 127-132;
if (race ne 2) or age<25 or age>64 or sex=1 then delete;
else do;
file out1;
put state $1-2 msapmsa $4-7 age 9-10 marital 12
pwgt1 14-17 rpob 19-20 yearsch 22-23 rlabor 25 rearning 27-32;
output;
end;