retain previous value

[Tom · Accepted solution]

If every other value is missing the that means the values the variable had when you called it also was alternating between missing and non-missing.

Here is simple example of how LAG() works.

71    data test;
72      set sashelp.class (obs=4);
73      lag_name=lag(name);
74      put (_n_ name lag_name) (=);
75    run;

_N_=1 Name=Alfred lag_name=
_N_=2 Name=Alice lag_name=Alfred
_N_=3 Name=Barbara lag_name=Alice
_N_=4 Name=Carol lag_name=Barbara

On first iteration (_N_=1) the value of ALFRED is saved and missing is returned.

Then on second (_N_=2) the value of ALICE is saves and ALFRED is returned.

etc.

[twildone]

That makes sense now. So far I have been able to get the output for thee first 2 rows....this is good.

[twildone]

Hi Tom....I was able to get the required table. It may not be the most efficient way to get the results. Thanks

data have;
    format ID Counts Counts1 ShowAmount NumberDays NumberDaysMonth NumberDaysBeg NumberDaysEnd BegAmount best12.;
    infile datalines4 dlm='7F'x missover dsd;
    input ID Counts Counts1 ShowAmount NumberDays NumberDaysMonth NumberDaysBeg NumberDaysEnd BegAmount : best32.;
datalines4;
152 1 1 . 36 28 0 28 3650
152 1 2 . 36 8 0 8 3650
152 2 3 . 85 14 0 14 4100
152 2 4 . 85 31 0 31 4100
152 2 5 700 85 30 10 20 4100
152 2 6 . 85 10 0 10 4100
152 3 7 . 71 19 0 19 4650
152 3 8 450 71 29 28 1 4650
152 3 9 . 71 23 0 23 4650
152 4 10 .85 25 0 25 4650
152 4 11 .85 36 0 31 4650
152 4 12 .85 29 0 29 4650
;;;;

data have;
	set have;
		if ShowAmount = .
			then ShowAmount = 0;
run;

data have(drop=NumberDaysMonth2 NumberDays2);
	set have;
	by ID Counts Counts1 notsorted;
		NumberDaysMonth2 = lag(NumberDaysMonth);
		NumberDays2 = lag(NumberDays1);
		NumberDays1 = NumberDays2 - NumberDaysMonth2;
		if Counts ^=lag(Counts) then 
			NumberDays1 = NumberDays;
run;

data want;
	set have;
	by ID Counts Counts1 notsorted;
		if first.ID and first.Counts and first.Counts1 then do;
			BegAmount1 = BegAmount;
			EndAmount = (BegAmount + ShowAmount);	
			NumberDays1 = NumberDays1;
			MonthlyAmount = (((NumberDaysBeg/NumberDays1)*BegAmount1) + ((NumberDaysEnd/NumberDays1)*EndAmount));
            EndAmount1 = (EndAmount - MonthlyAmount); 
		end;
run;

data want1;
	set want;
		if not first.Counts1 then 
			EndAmount2 = lag(EndAmount1);
			else EndAmount2 = EndAmount2;
		if not first.Counts1 then 
			BegAmount2 = lag(BegAmount);
			else BegAmount2 = BegAmount2;
		if not first.Counts1 then 
			NumberDays2 = lag(NumberDays1);
			else NumberDays2 = NumberDays2;
		if not first.Counts1 then 
			NumberDaysMonth2 = lag(NumberDaysMonth);
			else NumberDaysMonth2 = NumberDaysMonth2;
		if not first.Counts1 then 
			Counts2 = lag(Counts);
			else Counts2 = Counts;
		if missing(BegAmount1) then
			BegAmount1 = BegAmount - BegAmount2 + EndAmount2;
			else BegAmount1 = BegAmount1;
		if missing(EndAmount) then 
			EndAmount = BegAmount - BegAmount2 + ShowAmount + EndAmount2;
			else EndAmount = EndAmount;
		if missing(NumberDays1) and Counts = Counts2 then 
			NumberDays1 = NumberDays2 - NumberDaysMonth2;
			else NumberDays1 = NumberDays1;
		if missing(	MonthlyAmount) then 
			MonthlyAmount = (((NumberDaysBeg/NumberDays1)*BegAmount1) + ((NumberDaysEnd/NumberDays1)*EndAmount));
			else MonthlyAmount = MonthlyAmount;
		if missing(EndAmount1) then
            EndAmount1 = (EndAmount - MonthlyAmount);
		else EndAmount1 = EndAmount1;
		output;
run;

%macro test(likevar);
%do i=1 %to 10;

data want1;
	set want1;
		if not first.Counts1 then 
			EndAmount2 = lag(EndAmount1);
			else EndAmount2 = EndAmount2;
		if not first.Counts1 then 
			BegAmount2 = lag(BegAmount);
			else BegAmount2 = BegAmount2;
		if not first.Counts1 then 
			NumberDays2 = lag(NumberDays1);
			else NumberDays2 = NumberDays2;
		if not first.Counts1 then 
			NumberDaysMonth2 = lag(NumberDaysMonth);
			else NumberDaysMonth2 = NumberDaysMonth2;
		if not first.Counts1 then 
			Counts2 = lag(Counts);
			else Counts2 = Counts2;
		if missing(BegAmount1) then
			BegAmount1 = BegAmount - BegAmount2 + EndAmount2;
			else BegAmount1 = BegAmount1;
		if missing(EndAmount) then 
			EndAmount = BegAmount - BegAmount2 + ShowAmount + EndAmount2;
			else EndAmount = EndAmount;
		if missing(NumberDays1) and Counts = Counts2 then 
			NumberDays1 = NumberDays2 - NumberDaysMonth2;
			else NumberDays1 = NumberDays1;
		if missing(	MonthlyAmount) then 
			MonthlyAmount = (((NumberDaysBeg/NumberDays1)*BegAmount1) + ((NumberDaysEnd/NumberDays1)*EndAmount));
			else MonthlyAmount = MonthlyAmount;
		if missing(EndAmount1) then
            EndAmount1 = (EndAmount - MonthlyAmount);
		else EndAmount1 = EndAmount1;
		output;
run;
%end;

%mend test;
%test