topic Re: importing Hierarchical Files in SAS Studio

importing Hierarchical Files

u39734216 — Mon, 19 Aug 2019 11:39:47 GMT

Hi All,

the file contains different types of information for each Client_id, State, DoB in header row and different purchases carried out on different dates in transaction row. Import the data into SAS by retaining client_id and State such that

i) Only transaction data is imported

ii) Only header data is imported

H 1095 NJ 06DEC84
C 01DEC11 $45.0
C 01AUG11 $37.5
H 1096 CA 01SEP83
C 01JUL11 $156.7
H 1097 VG 07JUL74
C 01FEB11 $109.5
H 1099 OT 13FEB79
C 01Mar11 $45.0
C 01May11 $45.0

Guys can you help me how to import this. I have tried by a lot of ways but I couldn't do this.

Re: importing Hierarchical Files

PeterClemmensen — Mon, 19 Aug 2019 11:53:35 GMT

Just to be sure, the transaction data are the rows with dollar amounts in them and the header data are the rows without dates in them, correct?

Re: importing Hierarchical Files

PeterClemmensen — Mon, 19 Aug 2019 11:58:23 GMT

Also, do you have the indicator variable with H or C values in your actual data?

Re: importing Hierarchical Files

andreas_lds — Mon, 19 Aug 2019 11:58:27 GMT

Importing only transaction data makes not sense (from my point of view).

You need multiple input-statements: the first reads only the row-type. Then, depending on the value, you use an input-statement for reading h or c rows.

Here is a data step reading everything:

data everything;
   length 
      RowType $ 1
      ClientId 8
      State $ 2
      DoB 8
      PurchaseDate 8
      Amount 8
   ;
   retain ClientId State DoB;

   format Dob PurchaseDate Date.;
   informat 
      Dob PurchaseDate Date.
      Amount comma.
   ;

   drop RowType;

   input RowType @;

   if RowType = 'H' then do;
      input ClientID State DoB;
   end;
   else do;
      if RowType = 'C' then do;
         input PurchaseDate Amount;
         output;
      end;
   end;

datalines;
H 1095 NJ 06DEC84
C 01DEC11 $45.0
C 01AUG11 $37.5
H 1096 CA 01SEP83
C 01JUL11 $156.7
H 1097 VG 07JUL74
C 01FEB11 $109.5
H 1099 OT 13FEB79
C 01Mar11 $45.0
C 01May11 $45.0
;
run;

Re: importing Hierarchical Files

novinosrin — Mon, 19 Aug 2019 12:09:14 GMT

"Importing only transaction data makes not sense (from my point of view)."

Re: importing Hierarchical Files

ballardw — Mon, 19 Aug 2019 16:23:21 GMT

@u39734216 wrote:

Hi All,

the file contains different types of information for each Client_id, State, DoB in header row and different purchases carried out on different dates in transaction row. Import the data into SAS by retaining client_id and State such that

i) Only transaction data is imported

ii) Only header data is imported

H 1095 NJ 06DEC84
C 01DEC11 $45.0
C 01AUG11 $37.5
H 1096 CA 01SEP83
C 01JUL11 $156.7
H 1097 VG 07JUL74
C 01FEB11 $109.5
H 1099 OT 13FEB79
C 01Mar11 $45.0
C 01May11 $45.0

Guys can you help me how to import this. I have tried by a lot of ways but I couldn't do this.

If this is reading a text file of some sort then you should provide an example of what that text file actually looks like, the actual file not some value from it. Copy from the text file and paste into a code box opened on the forum using the {I} icon. Edit any sensitive names with something like XXXXX or YYYY as needed, but the actual layout of the file is often important.

Also you may want to have a discussion with the originator about the not-quite-up-to-snuff use of 2-digit years.

Re: importing Hierarchical Files

JackHamilton — Mon, 19 Aug 2019 14:54:18 GMT

I think I would use SELECT instead of IF THEN ELSE to handle the record type. That would automatically signal an error if an unexpected code shows up, and will be easier to expand if the next homework assignment is more complicated.