topic Re: Problem in using single trailing method with do loop in SAS Studio

Problem in using single trailing method with do loop

u39734216 — Sun, 18 Aug 2019 07:29:46 GMT

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

Re: Problem in using single trailing method with do loop

PeterClemmensen — Sun, 18 Aug 2019 07:39:02 GMT

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;

Re: Problem in using single trailing method with do loop

u39734216 — Sun, 18 Aug 2019 07:58:32 GMT

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

Re: Problem in using single trailing method with do loop

PeterClemmensen — Sun, 18 Aug 2019 08:00:02 GMT

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 🙂

Re: Problem in using single trailing method with do loop

u39734216 — Sun, 18 Aug 2019 10:26:29 GMT

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

Re: Problem in using single trailing method with do loop

novinosrin — Sun, 18 Aug 2019 19:31:22 GMT

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
;