Create semi-cumulative columns based off several other columns. SAS

user_16376398 0
Viewed 41

I've got some data which is essentially lots of columns of information/data and dates and then two columns of numbers and a column which is a flag (ie its either a 1 or a 0). Each row is information on an individual at a particular month.

For the two columns of numbers I want to create two new columns which are the cumulative numbers for each individual over time. And for the flag I want it to be 1 for all future dates for that individual once it has first become 1 for that individual.

I'm struggling to word this (and so also to google what I want to do!) so I've put what I have and what I want below. In this example: A1, B1, C1 would be one individual and A1, B2, C3 would be another individual.

I've got this:

Col1 Col2 Col3 Date Value_1 Value_2 Flag
A1 B1 C1 01Jan2021 0 100 0
A1 B1 C1 01Feb2021 0 0 0
A1 B1 C1 01Mar2021 10 100 0
A1 B1 C1 01Apr2021 50 0 0
A1 B1 C1 01May2021 0 10 1
A1 B1 C1 01Jun2021 10 0 0
A1 B1 C1 01Jul2021 0 0 0
A1 B2 C3 01Jan2021 0 0 0
A1 B2 C3 01Feb2021 0 20 1
A1 B2 C3 01Mar2021 10 20 0
A1 B2 C3 01Apr2021 40 20 0
A1 B2 C3 01May2021 0 0 0
A1 B2 C3 01Jun2021 30 0 0
A1 B2 C3 01Jul2021 0 0 0

And I want this:

Col1 Col2 Col3 Date Value_1_full Value_2_full Flag
A1 B1 C1 01Jan2021 0 100 0
A1 B1 C1 01Feb2021 0 100 0
A1 B1 C1 01Mar2021 10 200 0
A1 B1 C1 01Apr2021 60 200 0
A1 B1 C1 01May2021 60 210 1
A1 B1 C1 01Jun2021 70 210 1
A1 B1 C1 01Jul2021 70 210 1
A1 B2 C3 01Jan2021 0 0 0
A1 B2 C3 01Feb2021 0 20 1
A1 B2 C3 01Mar2021 10 40 1
A1 B2 C3 01Apr2021 50 60 1
A1 B2 C3 01May2021 50 60 1
A1 B2 C3 01Jun2021 80 60 1
A1 B2 C3 01Jul2021 80 60 1

I could do this if the only data I had was for a single individual, but there's lots of them. The code I've written is just giving me the total cumulative of the column - I can't figure out how to calculate them separately for each individual. I'm also struggling to write the code for the flag column for a similar reason. I've put the code below and would be very appreciative of any help/advice.

Note: I'm really new to SAS and to write this question I've struggled to get the date field in correctly by just typing out the data for this example (I've used this "Ignore" bit of the code below as a work around to get it into SAS) so if you could let me know what I've done wrong here that would also be greatly appreciated for the future!

data data_1;
input Col1 $ Col2 $ Col3 $ Date date8. Ignore Value_1 Value_2 Flag;
format Date date8.;
datalines;
A1 B1 C1 "'01Jan2021'd" 0 100 0
A1 B1 C1 "'01Feb2021'd" 0 0 0
A1 B1 C1 "'01Mar2021'd" 10 100 0
A1 B1 C1 "'01Apr2021'd" 50 0 0
A1 B1 C1 "'01May2021'd" 0 10 1
A1 B1 C1 "'01Jun2021'd" 10 0 0
A1 B1 C1 "'01Jul2021'd" 0 0 0
A1 B2 C3 "'01Jan2021'd" 0 0 0
A1 B2 C3 "'01Feb2021'd" 0 20 1
A1 B2 C3 "'01Mar2021'd" 10 20 0
A1 B2 C3 "'01Apr2021'd" 40 20 0
A1 B2 C3 "'01May2021'd" 0 0 0 
A1 B2 C3 "'01Jun2021'd" 30 0 0
A1 B2 C3 "'01Jul2021'd" 0 0 0
;
run;

Data data_2;
set data_1;
drop Ignore;
run;


proc sort data=data_2
    out=data_3;
    by Col1 Col2 Col3 Date;
run;


data data_4;
    set data_3;
    by Col1 Col2 Col3 Date;
 
    retain Col1 Col2 Col3 Date Value_1 Value_2 Flag Value_1_full Value_2_full;
 
    if first.Col1 AND first.Col2 AND first.Col3 AND first.Date then Value_1_full = Value_1;
    else Value_1_full = Value_1_full + Value_1;
run;
sas
1 Answers

So you're pretty close! I think this gets there...

proc sort data=data_1(drop=ignore)
    out=data_3;
    by Col1 Col2 Col3 Date;
run;


data data_4;
    set data_3;
    by Col1 Col2 Col3 Date;
 
    retain Col1 Col2 Col3 Date Value_1 Value_2 Flag Value_1_full Value_2_full;
 
    if first.Col3 then Value_1_full = Value_1;
    else Value_1_full = Value_1_full + Value_1;


    if first.col3 then flag=0;
    flag = max(flag,flag_Early);

run;

Only a few small changes. I removed one pointless data step (The drop can be done in any of the other places you use the data) and change the if first. to be if first.col3.

You don't need col2 and col1 - first.col3 is what you care about, the other two changing would also cause first.col3 to also be true by default.

you also don't want First.date there - first.date is true EVERY TIME the date changes (or any other variable before it in the by), and that happens on every row, so it is always true! You don't want that.

Finally, for flag you need to make a new variable. Old variables are in fact always retained! But they're also replaced every iteration with new values. So we rename it to flag_early or whatever you like, and use the max function to assign a 1 to flag any time flag_early has a 1 or keep the 1 in flag if it has it from before - again resetting it every time first.col3 is true.

user_16230070
Related