BookmarkSubscribeRSS Feed
Melody1027
Calcite | Level 5

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

 

Write a DATA step that will perform the following activities:
Create a SAS data set named Members. Members must be stored in the Work library of SAS Studio.
Read the records from Survey.txt into Members.
Create only one observation for each household in Survey.txt.
Each created observation in Members must contain only these variables but not necessarily in the given order:
o The identity of the respective household
o The type of housing of the respective household
o The number of members in the household (0 for a household without members)
o The number of male members(0 for a household without members)
o The maximum achieved education level among all members in the household (0 for a household without members)
o The total monthly income of the household (0 for a household without members)
o The number of members in the household is at least 18 years old as of 1 January
2023 (0 for a household without members) {Hint: Use function INTCK to compute
4 REPLIES 4
Tom
Super User Tom
Super User

Task seems pretty simple, but your description of the source text is incomplete.  There are two key things that seems to be missing.

 

  • The lines are delimited by commas
  • The member lines follow immediate the household lines and you can imply that they represent the members of that household.

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              .                                    .                    .
Melody1027
Calcite | Level 5
Here are our expected answer for this question.
-we can't use proc
Thank you!
ballardw
Super User

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.

SAS Innovate 2025: Call for Content

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!

Submit your idea!

Mastering the WHERE Clause in PROC SQL

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.

Discussion stats
  • 4 replies
  • 743 views
  • 0 likes
  • 3 in conversation