Reading raw files

[RichaRashmi]

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.

[Reeza]

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;

[novinosrin]

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
;

[ballardw · Accepted solution]

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.

[RichaRashmi]

This is perfect. Thank You!

[emrancaan]

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;

[MarkWik]

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

[emrancaan]

@MarkWik   Thanks for your valuable input.