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.
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.
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;
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
;
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.
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;
@emrancaan 3 passes for a simple reading is rather not desirable. It would only make it very time consuming
@MarkWik Thanks for your valuable input.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.