sas Reading Hierarchical Files

Question

I got this hierarchical files

A123456789,A,3    
Y,15FEB1980,M,M,3,FT,55000    
N,3JUN1982,F,M,3,UE,0    
N,24JAN2005,M,S,2,NA,0    
A135790234,B,1    
Y,19OCT1950,F,D,0,PT,5000    
B234523456,A,2    
N,21MAY1975,M,M,2,FT,30000    
Y,30JUN1978,F,M,1,PT,10000    
C345678901,A,0

and I want to made it into this

And I just write my code like this

But I got the result like this

How can I delete those 0 in the first row

Why did you post photographs of your desired result and program instead of just posting it as text? — Tom, Oct 31 '19 at 19:06
When you paste images it means we have to type out the full code and data from scratch and you reduce the number of people willing to answer your question. You're obviously free to include only images but be aware that's what will happen if you don't post them as text instead. Please review the instructions on how to ask a question [ask] — Reeza, Oct 31 '19 at 19:52

score 2 · Answer 1 · answered Oct 31 '19 at 17:26

Please add more details about the fields. Here is what I can get by guessing.

data test;
   infile cards dsd;
   input ID:$11. UNK:$1. subrecords;
   if subrecords eq 0 then output;
   else do i = 1 to subrecords;
      input (f1-f7)(:$10.);
      output;
      end;
   cards;   
A123456789,A,3
Y,15FEB1980,M,M,3,FT,55000
N,3JUN1982,F,M,3,UE,0
N,24JAN2005,M,S,2,NA,0
A135790234,B,1
Y,19OCT1950,F,D,0,PT,5000
B234523456,A,2
N,21MAY1975,M,M,2,FT,30000
Y,30JUN1978,F,M,1,PT,10000
C345678901,A,0
;;;;
   run;
proc print;
   run;

score 0 · Answer 2 · answered Nov 01 '19 at 03:34

Presuming the data file is robust and contains no misinformation...

You want one record per ID group and the first record in the group indicated the number of people in the group.

Input the household 'header' record
In a loop use a separate INPUT statement to read the person information
Use logic evaluation result (0,1) to compute sex counts and employed counts
compute average after the loop
use implicit output to create one record per household

data want;
  infile '...\households.dat' dlm=','; 

  attrib
    householdId   length=$10
    householdType length=$1
    memberCount   length=8
    householdHeadFlag length=$1
    dob informat=date9. format=date9.
    sex length=$1    
    maritalStatus length=$1 
    as length=$1
    employmentStatus length=$2
    income length=8        
  ; 

  input householdId householdType memberCount;

  femaleCount = 0;
  maleCount = 0;
  employedCount = 0;
  totalIncome = 0;
  do index = 1 to memberCount;
    input householdHeadFlag dob sex maritalStatus employmentStatus income;

    femaleCount + (sex = 'F');
    maleCount + (sex = 'M');
    employedCount + (employmentStatus in ('FT', 'PT'));
    totalIncome + income;        
  end;

  avgIncome = totalIncome / memberCount;

  keep householdId householdType memberCount femaleCount maleCount employedCount avgIncome;
run;

sas Reading Hierarchical Files

2 Answers2