Good day SAS community forum users,
I have a SAS data step question that I am hoping you can help me with. It concerns collapsing over observations, for drug treatment data with regimen_id’s and prescription start and stop dates.
Suppose I have the following structure:
Patient_id | Regimen_ID | Start_date | Stop_date |
1 | 1 | A0 | A1 |
1 | 1 | B0 | B1 |
1 | 1 | C0 | C1 |
1 | 2 | A0 | A1 |
1 | 2 | B0 | B1 |
And instead, I wanted to collapse start and stop dates over each combination of patient and regimen, so that I could generate the following (note the start date is the very first one for the regimen and the stop date is the very last for the regimen):
Patient_id | Regimen | Start_date | Stop_date |
1 | 1 | A0 | C1 |
1 | 2 | A0 | B1 |
How would this be done in SAS? I am a bit stumped.
Thanks again for your help,
-Carmine
The following should work:
data want (drop=_:); set have (rename=(Start_date=_Start_date Stop_date=_Stop_date)); retain Start_date; by Patient_id Regimen_ID; if first.Regimen_ID then Start_date=_Start_date; if last.Regimen_ID then do; Stop_date=_Stop_date; output; end; run;
Art, CEO, AnalystFinder.com
The following should work:
data want (drop=_:); set have (rename=(Start_date=_Start_date Stop_date=_Stop_date)); retain Start_date; by Patient_id Regimen_ID; if first.Regimen_ID then Start_date=_Start_date; if last.Regimen_ID then do; Stop_date=_Stop_date; output; end; run;
Art, CEO, AnalystFinder.com
Thank you for sharing this! It worked like a charm. This is greatly appreciated!
All the best,
-Carmine
This is a technique that uses the lag function to hold needed values rather than a temporary variable. Much more compact in cases like this.
data have;
input Patient_id Regimen_ID Start_date :$2. Stop_date :$2.;
put (_all_) (=);
datalines;
1 1 A0 A1
1 1 B0 B1
1 1 C0 C1
1 2 A0 A1
1 3 A0 A1
1 3 B0 B1
run;
data want;
set have;
by patient_id regimen_id;
if first.regimen_id or last.regimen_id;
if first.regimen_id ^=last.regimen_id then start_date=lag(start_date);
if last.regimen_id;
run;
Notes:
By the way, the single statement
if first.regimen_id^=last.regimen_id then start_date=lag(start_date);
is NOT EQUIVALENT to the pair of statements
if first.regimen_id>last.regimen_id then start_date=lag(start_date);
if first.regimen_id<last.regimen_id then start_date=lag(start_date);
because the single statement maintains only one queue of start_date values, alternating between values of the first. and last. records. In contrast, the two statements maintain two separate queues, one with the sequence of first. records, and the other with the sequence of last. records.
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.