Hello all,
I want to look at the rows in my table and assign a counting variable to increase by 1 if a certain set of conditions are met.
In the following table, for each study ID WANT needs to start counting at the LEGACY value. PLT lists the initial donation number for each participant. Then it needs to increase by 1 if the DonationType=1. If the donation type is ^=1 then the cell needs to be missing. Right now, I can get it to fill in the correct value for the first and instance of each study ID, but it will not fill down.
STUDYID | DonationNumb | DONTYPE | LEGACY | PLT | WANT | HAVE |
12 | 1 | 1 | 9 | 10 | 10 | 10 |
12 | 2 | 1 | . | 11 | 11 | |
12 | 3 | 2 | . | . | ||
12 | 4 | 1 | . | 12 | ||
12 | 5 | 1 | 13 | |||
12 | 6 | 1 | . | 14 | ||
13 | 1 | 2 | 0 | 0 | . | 0 |
13 | 2 | 1 | . | 1 | 1 | |
13 | 3 | 1 | . | 2 |
Data want;
set have;
retain HAVE;
HAVE=lag(HAVE);
if DONATIONNUMB=1 then HAVE=PLT;
if (DONATIONNUMB>1 AND DONATIONTYPE=1) then HAVE=(HAVE)+1;
else if(DONATIONNUMB>1 AND DONATIONTYPE>1) then HAVE=HAVE+0;
run;
data want;
set have;
by studyid;
if first.studyid
then do;
_have = plt;
if dontype = 1
then have = _have;
else have = .;
end;
else do;
if dontype = 1
then do;
_have + 1; /* sum statement causes automatic retain */
have = _have;
end;
else have = .;
end;
drop _have;
run;
The LAG() can't be reasonably used with a newly created variable; it fills its queue every time it is called, and at that moment the variable is still missing, or has the previous value anyway, because of the RETAIN.
data want;
set have;
by studyid;
if first.studyid
then do;
_have = plt;
if dontype = 1
then have = _have;
else have = .;
end;
else do;
if dontype = 1
then do;
_have + 1; /* sum statement causes automatic retain */
have = _have;
end;
else have = .;
end;
drop _have;
run;
The LAG() can't be reasonably used with a newly created variable; it fills its queue every time it is called, and at that moment the variable is still missing, or has the previous value anyway, because of the RETAIN.
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.