Amend a Loop

Accepted Solution Solved
Reply
Contributor
Posts: 64
Accepted Solution

Amend a Loop

FACTORDATEMONTHAMOUNT
TECH01-Feb-11125064.3
TECH01-Feb-11277980.36
TECH01-Feb-11344531.48
TECH01-Feb-11470708.78
TECH01-Feb-11587050.04
TECH01-Feb-11656310.83
TECH01-Feb-117152429.35
TECH01-Feb-11890115.41
TECH01-Feb-11980930.07
TECH01-Feb-111079364.52
TECH01-Feb-1111175092.67
TECH01-Feb-1112101078.03
TECH01-Feb-111391643.83
TECH01-Feb-111464509.84
TECH01-Feb-111515164.82
TECH01-Feb-111698763.39
TECH01-Feb-11171359.03
TECH01-Feb-11191675
TECH01-Feb-1120891.72
TECH01-Feb-11211045
TECH01-Feb-1124325.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-11125064.3
High-Tech Facility       01-Feb-11277980.36
High-Tech Facility       01-Feb-11344531.48
High-Tech Facility       01-Feb-11470708.78
High-Tech Facility       01-Feb-11587050.04
High-Tech Facility       01-Feb-11656310.83
High-Tech Facility       01-Feb-117152429.4
High-Tech Facility       01-Feb-11890115.41
High-Tech Facility       01-Feb-11980930.07
High-Tech Facility       01-Feb-111079364.52
High-Tech Facility       01-Feb-1111175092.7
High-Tech Facility       01-Feb-1112101078
High-Tech Facility       01-Feb-111391643.83
High-Tech Facility       01-Feb-111464509.84
High-Tech Facility       01-Feb-111515164.82
High-Tech Facility       01-Feb-111698763.39
High-Tech Facility       01-Feb-11171359.03
High-Tech Facility       01-Feb-11181359.03
High-Tech Facility       01-Feb-11191675
High-Tech Facility       01-Feb-1120891.72
High-Tech Facility       01-Feb-11211045
High-Tech Facility       01-Feb-11221045
High-Tech Facility       01-Feb-11231045
High-Tech Facility       01-Feb-1124325.79

 

Thanks

Aidan 


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

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;

View solution in original post


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

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 Smiley Very Happy 

 

Thak you kindly for this reply, works perfectly.

Occasional Contributor
Posts: 13

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: 13

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 Smiley Happy

 

Super User
Posts: 10,018

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,018

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
  • 505 views
  • 5 likes
  • 3 in conversation