DATA Step, Macro, Functions and more

Solved
Occasional Contributor
Posts: 5

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

```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.

All Replies
Super User
Posts: 23,937

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

``````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

```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

This is perfect. Thank You!
Contributor
Posts: 36

``````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

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

Contributor
Posts: 36