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.
That makes sense now. So far I have been able to get the output for thee first 2 rows....this is good.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.