topic Re: Reading raw files in SAS Programming

Reading raw files

RichaRashmi — Tue, 01 May 2018 14:55:22 GMT

Hello All,

I have a raw file with data like this:

108263 $946.00 $903.00 $804.00 $674.00 $663.00 $195.00 $922.00 $595.00 $157.00 $415.00 $868.00 $750.00
103681 $135.00 $573.00 $642.00 $208.00 $922.00 $592.00 $425.00 $658.00 $131.00 $648.00 $750.00 $515.00
116865 $624.00 $679.00 $402.00 $636.00 $358.00 $560.00 $884.00 $514.00 $565.00 $278.00 $117.00 $852.00
102998 $747.00 $505.00 $549.00 $942.00 $884.00 $991.00 $480.00 $326.00 $447.00 $617.00 $721.00 $874.00

It represents a clientID and some bills for 12 months.

I need the output to be something like this:

ID Bill Month
108263 $946.00 01
108263 $903.00 02
108263 $804.00 03
108263 $674.00 04
108263 $663.00 05
108263 $195.00 06

How should I go about it?

Any help is appreciated.

Re: Reading raw files

Reeza — Tue, 01 May 2018 15:02:31 GMT

I'd probably read it as is and then use PROC TRANSPOSE to transpose it to a long format.

Or you can use the trailing @@ in your INPUT statement to keep the line held there using a do loop, especially if you know ahead of time the number of columns.

data want;
infile 'path to your file.txt' dlm=' ';
informat Value dollar12.2;

input ID @;
do i=1 to 12;
input Value @@;
output;
end;

run;

Re: Reading raw files

novinosrin — Tue, 01 May 2018 15:13:32 GMT

data have;
infile datalines truncover;
input id   Bill : comma10. @;
call missing(month);
do while(not missing(bill));
month+1;
output;
input     Bill : comma10. @ ;
end;
datalines;
108263 $946.00 $903.00 $804.00 $674.00 $663.00 $195.00 $922.00 $595.00 $157.00 $415.00 $868.00 $750.00 
103681 $135.00 $573.00 $642.00 $208.00 $922.00 $592.00 $425.00 $658.00 $131.00 $648.00 $750.00 $515.00 
116865 $624.00 $679.00 $402.00 $636.00 $358.00 $560.00 $884.00 $514.00 $565.00 $278.00 $117.00 $852.00 
102998 $747.00 $505.00 $549.00 $942.00 $884.00 $991.00 $480.00 $326.00 $447.00 $617.00 $721.00 $874.00
;

Re: Reading raw files

ballardw — Tue, 01 May 2018 15:56:36 GMT

An example reading inline data:

data want;
   informat id $8. bill comma12.2;
   format bill dollar12.2;
   input id @;
   do month=1 to 12;
      input bill @;
      output;
   end;
   input;

datalines;
108263 $946.00 $903.00 $804.00 $674.00 $663.00 $195.00 $922.00 $595.00 $157.00 $415.00 $868.00 $750.00 
103681 $135.00 $573.00 $642.00 $208.00 $922.00 $592.00 $425.00 $658.00 $131.00 $648.00 $750.00 $515.00 
116865 $624.00 $679.00 $402.00 $636.00 $358.00 $560.00 $884.00 $514.00 $565.00 $278.00 $117.00 $852.00 
102998 $747.00 $505.00 $549.00 $942.00 $884.00 $991.00 $480.00 $326.00 $447.00 $617.00 $721.00 $874.00
;
run;

Your program would have an infile statement pointing to the source data file and not have the datalines section.

If you want the month to appear with a leading 0 assign a Z2. format.

Or perhaps create a actual date with date=mdy(1,month,2017); or appropriate year value and assign a date format.

Re: Reading raw files

emrancaan — Tue, 01 May 2018 16:08:18 GMT

Data have;
Input ID $ jan $ feb $  mar $ apr $ may $ jun $ jul $ aug $ sep $ oct $ nov $ dec $;
datalines;
108263 $946.00 $903.00 $804.00 $674.00 $663.00 $195.00 $922.00 $595.00 $157.00 $415.00 $868.00 $750.00
103681 $135.00 $573.00 $642.00 $208.00 $922.00 $592.00 $425.00 $658.00 $131.00 $648.00 $750.00 $515.00
116865 $624.00 $679.00 $402.00 $636.00 $358.00 $560.00 $884.00 $514.00 $565.00 $278.00 $117.00 $852.00
102998 $747.00 $505.00 $549.00 $942.00 $884.00 $991.00 $480.00 $326.00 $447.00 $617.00 $721.00 $874.00
;
run;
proc sort data=have; by id; run; 
Proc transpose data=have out=want;
 by ID ;
 var jan  feb   mar  apr  may  jun  jul  aug  sep  oct  nov  dec ;
run;

Re: Reading raw files

MarkWik — Tue, 01 May 2018 16:11:06 GMT

@emrancaan 3 passes for a simple reading is rather not desirable. It would only make it very time consuming

Re: Reading raw files

RichaRashmi — Tue, 01 May 2018 18:51:20 GMT

This is perfect. Thank You!

Re: Reading raw files

emrancaan — Wed, 02 May 2018 08:54:30 GMT

@MarkWik Thanks for your valuable input.