topic Re: Moving sum with max value by id group in New SAS User

Moving sum with max value by id group

1SasUser1 — Tue, 18 Jun 2019 13:49:59 GMT

Hi,

I am using SAS Enterprise Guide 5.1.

I currently have millions of rows and thousands of different ID's. I would like the following (only 1 ID for an example):

id	month	Year	Level	What I want
A	8	17	1	1
A	9	17	0	0
A	10	17	1	1
A	11	17	1	1
A	12	17	1	1
A	1	18	0	0
A	2	18	1	0
A	3	18	0	0
A	4	18	1	1
A	5	18	1	1
A	6	18	0	0
A	7	18	1	1
A	8	18	1	0
A	9	18	0	0
A	10	18	0	0
A	11	18	1	1
A	12	18	0	0

I would like my computation to begin from when the month equals 1 and the year is 18.

I want to look back at the previous 5 months (already ordered by this - so previous 5 rows) and to sum column 'Level'. If this is greater than or equal to 3 then the new column of 'Level' (row 6) should be zero, only if the current column 'Level' is 1. If the column 'Level' is 0, it is irrelevant. I would like this to keep moving down until the last month of 12 and year 18.

This is my code I have tried, but it didn't work:

data work.test;
set WORK.MONTHS;

lag_Level = lag(Level);
lag_Level2 = lag2(Level);
lag_Level3 = lag3(Level);
lag_Level4 = lag4(Level);
lag_Level5 = lag5(Level);


if month eq 18 then  do;
if sum(lag_count,lag_count2,lag_count3,lag_count4,lag_count5,Level) ge 3 then prop_Level = 0;
end;
else prop_Level = Level;

run;

data WORK.SAMPLEDATA;
infile datalines dsd truncover;
input id:$1. month:BEST12. Year:BEST12. Level:BEST12.;
format month BEST12. Year BEST12. Level BEST12.;
datalines;
A 8 17 1
A 9 17 0
A 10 17 1
A 11 17 1
A 12 17 1
A 1 18 0
A 2 18 1
A 3 18 0
A 4 18 1
A 5 18 1
A 6 18 0
A 7 18 1
A 8 18 1
A 9 18 0
A 10 18 0
A 11 18 1
A 12 18 0
;;;;

Any help is greatly appreciated.

Thanks!

Re: Moving sum with max value by id group

PeterClemmensen — Tue, 18 Jun 2019 14:02:05 GMT

Just to understand the logic.. Why does your 'want' variable equal 1 in the row with values

? The sum of level for the previous five (though there is only four here) obs is 3?

Re: Moving sum with max value by id group

1SasUser1 — Tue, 18 Jun 2019 14:05:45 GMT

I will only be using the data for year 18, so I have just kept what the current value is in column 'Level'.

Apologies for any confusion. I just want the new column to have a different value from year 18 and month 1 on wards.

Thanks.

Re: Moving sum with max value by id group

PeterClemmensen — Tue, 18 Jun 2019 14:09:42 GMT

Ok. I understand. But then what about the obs

. I would think that your new variable would have zero here?

Re: Moving sum with max value by id group

1SasUser1 — Tue, 18 Jun 2019 14:15:08 GMT

Another valid point.

I have added the previous 5 rows of the new column and also the value in column 'Level'. This then equals 3, so the new column will be 1, as the maximum sum is 3.

If it was entirely using the column 'Level' then it would be zero.

Hope that makes more sense.