Header record of a household:
Household identity: 10-15 characters, always begin with an alphabetical letter in uppercase, such as A, B, etc.
Type of housing: Character ‘A’ for private, and ‘B’ for public
Household member’s record (if present):
Date of birth :10-11 characters in the form dd/mon/yyyy where dd is the day value which may be in the form of 1 or 2 digits.
Householder indicator: Character ‘Y’ for yes, and ‘N’ for no.
Gender : Characters of ‘Female’, or ‘Male’.
Marital status : Characters of ‘Married’, ‘Single’, ‘Divorced’, or ‘Unknown’ .
Achieved education level : Standard numeric with 0 for none, 1 for primary, 2 for secondary, 3 for tertiary.
Employment status: Characters of ‘FT’, ‘PT’, ‘UE’, or ‘NA’ .
Monthly income : Standard numeric.
Datalines:
A1234567BC012,A
15/FEB/1980,Y,Male,Married,3,FT,55000
3/JUN/1982,N,Female,Married,3,UE,0
24/JAN/2005,N,Male,Unknown,2,NA,0
D135EG023456789,B
19/OCT/1950,Y,Female,Divorced,0,PT,5000
X123A567F9,A
B2345234CC,A
21/MAY/1975,N,Male,Married,2,FT,30000
30/JUN/1978,Y,Female,Married,1,PT,10000
Task seems pretty simple, but your description of the source text is incomplete. There are two key things that seems to be missing.
So read in the household fields into variables and RETAIN those variables so the values persist when you read the member fields.
The tricky part is how to write a record for those households with no members (how is that possible? Perhaps there should just be a dummy record to indicate the house is empty?)
One way is to keep track of how many members have been found for the previous household and output it before reading the id of the new house hold. You also need to do the same test at the end of the data stream.
So first let's convert your example into an actual file we can use as input.
filename records temp;
options parmcards=records;
parmcards;
A1234567BC012,A
15/FEB/1980,Y,Male,Married,3,FT,55000
3/JUN/1982,N,Female,Married,3,UE,0
24/JAN/2005,N,Male,Unknown,2,NA,0
D135EG023456789,B
19/OCT/1950,Y,Female,Divorced,0,PT,5000
X123A567F9,A
B2345234CC,A
21/MAY/1975,N,Male,Married,2,FT,30000
30/JUN/1978,Y,Female,Married,1,PT,10000
LAST,A
;
I added an extra empty household at the end to test that boundary condition.
Program:
data want;
infile records dsd truncover end=eof;
length ID $15 TYPE $1 MEMBER 8 DOB 8 HEAD $1 GENDER $6 MARITAL $8 EDUC 8 EMPLOY $2 INCOME 8;
format dob date9.;
retain id type member;
input @;
if 'A' <=: _infile_ <=: 'Z' then do;
if member<1 and _n_>1 then output;
call missing(of _all_);
member=0;
input id type;
end;
else do;
member+1;
input dob :date. head gender marital educ employ income;
output;
end;
if eof and member<1 then output;
run;
Result
Obs ID TYPE MEMBER DOB HEAD GENDER MARITAL EDUC EMPLOY INCOME 1 A1234567BC012 A 1 15FEB1980 Y Male Married 3 FT 55000 2 A1234567BC012 A 2 03JUN1982 N Female Married 3 UE 0 3 A1234567BC012 A 3 24JAN2005 N Male Unknown 2 NA 0 4 D135EG023456789 B 1 19OCT1950 Y Female Divorced 0 PT 5000 5 X123A567F9 A 0 . . . 6 B2345234CC A 1 21MAY1975 N Male Married 2 FT 30000 7 B2345234CC A 2 30JUN1978 Y Female Married 1 PT 10000 8 LAST A 0 . . .
Show what you have completed or at least attempts so far and what specific questions you have about problems.
We can help where you have problems with specific tasks but doing your homework for you won't help you and we don't get any credit for the class.
If this involves reading a text file then provide examples of the actual text file by copy/paste text into a text box on this forum opened using the </> icon that appears above the message window. This may be critical because the main message windows on this forum reformat text and what you pasted as an "example" is quite likely not actually it appears in the text file.
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!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.