topic how to read heirarchial files in New SAS User

how to read heirarchial files

Raj00007 — Fri, 14 Aug 2020 10:00:31 GMT

i have a code like this

P 1095 SMITH, HOWARD                                                            
C 01-08-11 $45.0                                                                
C 01-17-11 $37.5                                                                
P 1096 BARCLAY, NICK                                                            
C 01-09-11 $150.5                                                               
P 1097 REISCH, DEBORAH                                                          
C 01-02-11 $109.0 
C 03-02-11 $100.0                                                              
P 1099 WILSON, ERNEST                                                           
C 01-03-11 $45.0                                                                
C 01-05-11 $45.0

and i need it to convert it to like this

Obs name id date sales
1 SMITH, HOWARD 1095 01/08/11 $45.00
2 SMITH, HOWARD 1095 01/17/11 $37.50
3 BARCLAY, NICK 1096 01/09/11 $150.50
4 REISCH, DEBORAH 1097 01/02/11 $109.00
5 REISCH, DEBORAH 1097 03/02/11 $100.00
6 WILSON, ERNEST 1099 01/03/11 $45.00
7 WILSON, ERNEST 1099 01/05/11 $45.00

how to get the result. Any leads?

Re: how to read heirarchial files

Jagadishkatam — Fri, 14 Aug 2020 10:34:13 GMT

Please try the below code

data have;
input col1:$ col2$ col3:&$100.;
cards;
P 1095 SMITH, HOWARD                                                            
C 01-08-11 $45.0                                                                
C 01-17-11 $37.5                                                                
P 1096 BARCLAY, NICK                                                            
C 01-09-11 $150.5                                                               
P 1097 REISCH, DEBORAH                                                          
C 01-02-11 $109.0 
C 03-02-11 $100.0                                                              
P 1099 WILSON, ERNEST                                                           
C 01-03-11 $45.0                                                                
C 01-05-11 $45.0      
;

data want;
set have;
retain name id;
if col1='P' then do;
name=col3;
id=col2;
end;
else if col1='C' then do;
date=input(col2,mmddyy10.);
sale=col3;
end;
format date mmddyys8.;
if col1='C';
drop col:;
run;

Re: how to read heirarchial files

BrunoMueller — Fri, 14 Aug 2020 11:03:23 GMT

In the beginning of SAS this type of data was very common, so the DATA Step can deal with this quite easy, see code sample below. The important part is the @ at the end of the INPUT statement, it will keep the current record in the input buffer.

So you first read the type of the record and then use the different INPUT statement to read the rest of the current line. You do need to use the RETAIN on the variables from the "header" line.

data have;
  infile cards dlm=" ";
  input
    type : $1.
    @
  ;
  retain id lname fname;
  if type = "P" then do;    
    input
      id : 8.
      lname : $32.
      fname : $32.
    ;
  end;

  if type = "C" then do;
    input 
      date : mmddyy.
      someDollar : dollar.
    ;
    output;
  end;
  format date date9.;
  *drop type;

  cards;
P 1095 SMITH, HOWARD                                                            
C 01-08-11 $45.0                                                                
C 01-17-11 $37.5                                                                
P 1096 BARCLAY, NICK                                                            
C 01-09-11 $150.5                                                               
P 1097 REISCH, DEBORAH                                                          
C 01-02-11 $109.0 
C 03-02-11 $100.0                                                              
P 1099 WILSON, ERNEST                                                           
C 01-03-11 $45.0                                                                
C 01-05-11 $45.0      
;