Problem in using single trailing method with do loop

[u39734216]

Hi,

“Client_ID_12_months_bill.txt” contains the 12 months credit card bill against each client_id in a single record. Import the data into SAS such that bill for each month is recorded as a separate observation and month number is also specified the data. guys can you help me. I don't know how to do it. I want the output in three columns client id months and bill.  pls, help me.

The data is like - 

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
115569 $254.00 $792.00 $420.00 $642.00 $851.00 $258.00 $872.00 $828.00 $658.00 $260.00 $499.00 $575.00
120252 $499.00 $514.00 $141.00 $870.00 $968.00 $191.00 $943.00 $788.00 $286.00 $213.00 $944.00 $752.00
102563 $167.00 $125.00 $976.00 $483.00 $517.00 $797.00 $879.00 $988.00 $910.00 $451.00 $468.00 $236.00
104280 $443.00 $785.00 $758.00 $911.00 $940.00 $276.00 $428.00 $706.00 $277.00 $936.00 $675.00 $268.00
114883 $983.00 $371.00 $483.00 $960.00 $890.00 $974.00 $619.00 $499.00 $219.00 $629.00 $918.00 $588.00
109022 $440.00 $739.00 $703.00 $729.00 $915.00 $634.00 $373.00 $511.00 $993.00 $427.00 $715.00 $261.00
100801 $267.00 $572.00 $309.00 $344.00 $990.00 $610.00 $775.00 $674.00 $658.00 $366.00 $441.00 $746.00
117016 $834.00 $130.00 $676.00 $211.00 $992.00 $426.00 $140.00 $301.00 $562.00 $874.00 $853.00 $742.00
120727 $980.00 $966.00 $730.00 $748.00 $600.00 $833.00 $205.00 $780.00 $615.00 $792.00 $574.00 $943.00
114764 $115.00 $315.00 $755.00 $599.00 $986.00 $450.00 $741.00 $467.00 $527.00 $693.00 $674.00 $781.00
117877 $794.00 $290.00 $758.00 $854.00 $494.00 $576.00 $877.00 $198.00 $485.00 $272.00 $914.00 $735.00
102649 $585.00 $659.00 $795.00 $789.00 $807.00 $983.00 $369.00 $323.00 $614.00 $155.00 $367.00 $274.00
119729 $855.00 $487.00 $657.00 $668.00 $683.00 $868.00 $143.00 $148.00 $648.00 $454.00 $219.00 $140.00
116952 $621.00 $750.00 $844.00 $296.00 $199.00 $959.00 $519.00 $266.00 $809.00 $908.00 $641.00 $588.00
118038 $515.00 $256.00 $169.00 $928.00 $203.00 $460.00 $609.00 $474.00 $665.00 $465.00 $865.00 $354.00

[PeterClemmensen · Accepted solution]

Insert path and filename 🙂

data want(keep=clientid month bill);
	infile "YourPathHere\file.txt";
	input clientid (month1-month12)(:comma8.2);
	array _{*} month:;
	do month=1 to dim(_);
		bill=_[month];
		output;
	end;
run;

[u39734216]

can you pls explain why did you use underscore and what does dim means? By the way thanks for the answer.

[PeterClemmensen]

The underscore is just the name of the array. Could be any valid SAS name. 

The Dim Function returns the dimension of the array, in this case 12.

Anytime, glad to help 🙂

[u39734216]

Can I use a single trailing method with do loop in this.

[novinosrin]

Hello @u39734216   

---

@u39734216 wrote:

Can I use a single trailing method with do loop in this.

---

I like your thinking

data want;
infile cards truncover;	/*replace the infile with appropriate path*/
input id $ bill : dollar10.2 @;
do month=1 by 1 while(month<countw(_infile_,' '));
 output;
 input bill : dollar10.2 @;
end;
format bill dollar10.2;
cards;
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
115569 $254.00 $792.00 $420.00 $642.00 $851.00 $258.00 $872.00 $828.00 $658.00 $260.00 $499.00 $575.00
120252 $499.00 $514.00 $141.00 $870.00 $968.00 $191.00 $943.00 $788.00 $286.00 $213.00 $944.00 $752.00
102563 $167.00 $125.00 $976.00 $483.00 $517.00 $797.00 $879.00 $988.00 $910.00 $451.00 $468.00 $236.00
104280 $443.00 $785.00 $758.00 $911.00 $940.00 $276.00 $428.00 $706.00 $277.00 $936.00 $675.00 $268.00
114883 $983.00 $371.00 $483.00 $960.00 $890.00 $974.00 $619.00 $499.00 $219.00 $629.00 $918.00 $588.00
109022 $440.00 $739.00 $703.00 $729.00 $915.00 $634.00 $373.00 $511.00 $993.00 $427.00 $715.00 $261.00
100801 $267.00 $572.00 $309.00 $344.00 $990.00 $610.00 $775.00 $674.00 $658.00 $366.00 $441.00 $746.00
117016 $834.00 $130.00 $676.00 $211.00 $992.00 $426.00 $140.00 $301.00 $562.00 $874.00 $853.00 $742.00
120727 $980.00 $966.00 $730.00 $748.00 $600.00 $833.00 $205.00 $780.00 $615.00 $792.00 $574.00 $943.00
114764 $115.00 $315.00 $755.00 $599.00 $986.00 $450.00 $741.00 $467.00 $527.00 $693.00 $674.00 $781.00
117877 $794.00 $290.00 $758.00 $854.00 $494.00 $576.00 $877.00 $198.00 $485.00 $272.00 $914.00 $735.00
102649 $585.00 $659.00 $795.00 $789.00 $807.00 $983.00 $369.00 $323.00 $614.00 $155.00 $367.00 $274.00
119729 $855.00 $487.00 $657.00 $668.00 $683.00 $868.00 $143.00 $148.00 $648.00 $454.00 $219.00 $140.00
116952 $621.00 $750.00 $844.00 $296.00 $199.00 $959.00 $519.00 $266.00 $809.00 $908.00 $641.00 $588.00
118038 $515.00 $256.00 $169.00 $928.00 $203.00 $460.00 $609.00 $474.00 $665.00 $465.00 $865.00 $354.00
;