DATA Step, Macro, Functions and more

Reading raw files

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

Reading raw files

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.


Accepted Solutions
Solution
‎05-01-2018 02:51 PM
Super User
Posts: 13,876

Re: Reading raw files

Posted in reply to RichaRashmi

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.

View solution in original post


All Replies
Super User
Posts: 23,937

Re: Reading raw files

Posted in reply to RichaRashmi

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;

Super User
Posts: 2,041

Re: Reading raw files

Posted in reply to RichaRashmi
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
;
Solution
‎05-01-2018 02:51 PM
Super User
Posts: 13,876

Re: Reading raw files

Posted in reply to RichaRashmi

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.

Occasional Contributor
Posts: 5

Re: Reading raw files

This is perfect. Thank You!
Contributor
Posts: 36

Re: Reading raw files

Posted in reply to RichaRashmi
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;
Frequent Contributor
Posts: 105

Re: Reading raw files

Posted in reply to emrancaan

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

Contributor
Posts: 36

Re: Reading raw files

@MarkWik   Thanks for your valuable input.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 188 views
  • 4 likes
  • 6 in conversation