Solved
Contributor
Posts: 64

# Amend a Loop

 FACTOR DATE MONTH AMOUNT TECH 01-Feb-11 1 25064.3 TECH 01-Feb-11 2 77980.36 TECH 01-Feb-11 3 44531.48 TECH 01-Feb-11 4 70708.78 TECH 01-Feb-11 5 87050.04 TECH 01-Feb-11 6 56310.83 TECH 01-Feb-11 7 152429.35 TECH 01-Feb-11 8 90115.41 TECH 01-Feb-11 9 80930.07 TECH 01-Feb-11 10 79364.52 TECH 01-Feb-11 11 175092.67 TECH 01-Feb-11 12 101078.03 TECH 01-Feb-11 13 91643.83 TECH 01-Feb-11 14 64509.84 TECH 01-Feb-11 15 15164.82 TECH 01-Feb-11 16 98763.39 TECH 01-Feb-11 17 1359.03 TECH 01-Feb-11 19 1675 TECH 01-Feb-11 20 891.72 TECH 01-Feb-11 21 1045 TECH 01-Feb-11 24 325.79

There was a gap between 21 and 24 month.

I wanted to solve this by using the code below which populated the month 22 and 23 with data, but how would I reset the AMOUNT column to 0 in the current code structure?

proc sort data =&_input1
out=temp1;
by FACTOR DATE MONTH;
run;

data temp3;
set temp1 end=done;
by FACTOR DATE MONTH;
output;
if done=0;
nextobs = _n_ + 1;
set temp1 (keep=MONTHrename=(MONTH=next_month)) point=nextobs;
if next_month > MONTH+ 1 then do MONTH=MONTH+1 to next_month-1;
output;
end;
drop nextobs;
run;

data &_output1;
set temp3 ;
run;

Out put is follows

 High-Tech Facility 01-Feb-11 1 25064.3 High-Tech Facility 01-Feb-11 2 77980.4 High-Tech Facility 01-Feb-11 3 44531.5 High-Tech Facility 01-Feb-11 4 70708.8 High-Tech Facility 01-Feb-11 5 87050 High-Tech Facility 01-Feb-11 6 56310.8 High-Tech Facility 01-Feb-11 7 152429 High-Tech Facility 01-Feb-11 8 90115.4 High-Tech Facility 01-Feb-11 9 80930.1 High-Tech Facility 01-Feb-11 10 79364.5 High-Tech Facility 01-Feb-11 11 175093 High-Tech Facility 01-Feb-11 12 101078 High-Tech Facility 01-Feb-11 13 91643.8 High-Tech Facility 01-Feb-11 14 64509.8 High-Tech Facility 01-Feb-11 15 15164.8 High-Tech Facility 01-Feb-11 16 98763.4 High-Tech Facility 01-Feb-11 17 1359.03 High-Tech Facility 01-Feb-11 18 1359.03 High-Tech Facility 01-Feb-11 19 1675 High-Tech Facility 01-Feb-11 20 891.72 High-Tech Facility 01-Feb-11 21 1045 High-Tech Facility 01-Feb-11 22 1045 High-Tech Facility 01-Feb-11 23 1045 High-Tech Facility 01-Feb-11 24 325.79

Thanks

Aidan

Accepted Solutions
Solution
‎07-12-2016 09:07 AM
Occasional Contributor
Posts: 19

## Re: Amend a Loop

To make sure I understand, are you saying that basically you want sas to create new rows for missing months and populate AMOUNT as 0?

If so all you need to do is add AMOUNT=0 in the last do loop, before the output statement. Like this:

``````data ds3;
set ds2 end=done;
by FACTOR DATE MONTH;
output;

if done=0;
nextobs = _n_ + 1;
set ds2 (keep=MONTH rename=(MONTH=next_month)) point=nextobs;
if next_month > MONTH+ 1 then do MONTH=MONTH+1 to next_month-1;
AMOUNT=0;
output;
end;
drop nextobs;
run;``````

All Replies
Solution
‎07-12-2016 09:07 AM
Occasional Contributor
Posts: 19

## Re: Amend a Loop

To make sure I understand, are you saying that basically you want sas to create new rows for missing months and populate AMOUNT as 0?

If so all you need to do is add AMOUNT=0 in the last do loop, before the output statement. Like this:

``````data ds3;
set ds2 end=done;
by FACTOR DATE MONTH;
output;

if done=0;
nextobs = _n_ + 1;
set ds2 (keep=MONTH rename=(MONTH=next_month)) point=nextobs;
if next_month > MONTH+ 1 then do MONTH=MONTH+1 to next_month-1;
AMOUNT=0;
output;
end;
drop nextobs;
run;``````
Contributor
Posts: 64

## Re: Amend a Loop

Oh my god you just made that seem so simple

Thak you kindly for this reply, works perfectly.

Occasional Contributor
Posts: 19

## Re: Amend a Loop

Ha no problem. Sometimes we (well at least I know I do) expect a complicated solution and miss the less complicated ones.

Contributor
Posts: 64

## Re: Amend a Loop

If I wanted to expand on this, lets say the max month is 60 (which it won't always be)

So currently the data runs up to month 24 but if I wanted to create blank data for each month up to month 60, can I amend the existing loop to achieve this?

e.g.

date          month    amount

1/feb/11     25          0

1/feb/11     26          0

1/feb/11     27          0

......

Sorry now, I should probably post a new query

Occasional Contributor
Posts: 19

## Re: Amend a Loop

This should do what you're looking for:

``````proc sort data=ds1 out=ds2; by FACTOR DATE MONTH; run;

%let maxmonth=60;

data ds3(keep=FACTOR DATE MONTH AMOUNT);
retain count;
set ds2 end=done;
by FACTOR DATE MONTH;
output;

count=sum(count,1);

test=done;

if done=0 then
do;
nextobs = _n_ + 1;
set ds2 (keep=MONTH rename=(MONTH=next_month)) point=nextobs;
if next_month > MONTH+ 1 then do MONTH=MONTH+1 to next_month-1;
AMOUNT=0;
count=sum(count,1);
output;
end;
end;
else
do MONTH=(count+1) to &maxmonth;
AMOUNT=0;
output;
end;
run;``````

Contributor
Posts: 64

## Re: Amend a Loop

e.g.

date          month    amount     factor_month

1/feb/11     25          0                    60

1/feb/11     26          0                    60

1/feb/11     27          0                    60

1/mar/11     25          0                    44

1/mar/11     26          0                    44

1/mar/11     27          0                    44

The 60 value can change depending on the month example above.

I think a tweak to the code would be required in this case.

It doesnt seem to work if I apply FACTOR_MONTH as maxmonth

Any ideas?

And thanks again

Super User
Posts: 10,784

## Re: Amend a Loop

``````data have;
infile cards expandtabs truncover;
input FACTOR \$	DATE : \$20.	MONTH	AMOUNT;
cards;
TECH	01-Feb-11	17	1359.03
TECH	01-Feb-11	19	1675
TECH	01-Feb-11	20	891.72
TECH	01-Feb-11	21	1045
TECH	01-Feb-11	24	325.79
;
run;
data want;
merge have have(keep=FACTOR MONTH rename=(FACTOR=F MONTH=M) firstobs=2);
output;
if FACTOR=F then do;
do i=MONTH+1 to M-1;
MONTH=i;AMOUNT=0;output;
end;
end;
drop i F M;
run;``````
Contributor
Posts: 64

## Re: Amend a Loop

Thanks for the reply

This soultion doens't fit unfortunately, the data currently goes up to month 24 but I need to create data up to a random variable factor_month which could be 60 ro 55 for example.

I am able to loop up to the max month which is available in the data per date, which is 24 as per data shown but cannot go any further

Thanks as always

Super User
Posts: 10,784

## Re: Amend a Loop

OK. Assuming I understand what you mean.

``````data have;
infile cards expandtabs truncover;
input FACTOR \$	DATE : \$20.	MONTH	AMOUNT;
cards;
TECH	01-Feb-11	17	1359.03
TECH	01-Feb-11	19	1675
TECH	01-Feb-11	20	891.72
TECH	01-Feb-11	21	1045
TECH	01-Feb-11	24	325.79
;
run;

%let factor_month=50;
data month;
do month=1 to &factor_month ;
output;
end;
run;

proc sql;
create table want as
select a.*,coalesce(b.AMOUNT,0) as AMOUNT
from
(
select *
from (select distinct FACTOR,DATE from have),(select month from month)
) as a left join have as b
on a.FACTOR=b.FACTOR and a.DATE=b.DATE and a.MONTH=b.MONTH;
quit;``````
Contributor
Posts: 64

## Re: Amend a Loop

Thank you that has been a help, think its okay now
☑ This topic is solved.

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

Discussion stats
• 10 replies
• 633 views
• 5 likes
• 3 in conversation