I am trying to gather dates from a dataset using the first.variable and last.variable from SAS. Here is the code to create a reproducible example:
data example;
infile datalines delimiter = ",";
input id $ code $ valid_from valid_to;
format valid_from IS8601DA10. valid_to IS8601DA10.;
datalines;
1A,ABC,20058,20177
1A,DEF,20178,20481
1A,DEF,20482,20605
1A,DEF,20606,21548
1A,DEF,21549,21638
1A,DEF,21639,21729
1A,ABC,21730,21733
1A,ABC,21734,21808
1B,MNO,20200,20259
1B,PQR,20260,20269
1B,STU,20270,20331
1B,VWX,20332,20361
1B,VWX,20362,22108
1B,VWX,22109,22164
1B,VWX,22165,22165
1B,VWX,22166,2936547
;
run;
The idea is to get, for each id, only one observation per code with the corresponding range of dates it cover.
Here is my code:
proc sort data=example out=example_sorted; by code valid_from; run;
data collapse_val_dates;
set example_sorted;
by code valid_from;
if first.code = 1 and last.code = 1 then do;
output;
end;
if first.code = 1 and last.code = 0 then do;
hold = valid_from;
retain hold;
end;
if first.code = 0 and last.code = 1 then do;
valid_from = hold;
output;
end;
drop hold;
run;
Here is the result (table collapse_val_dates):
+----+------+------------+------------+
| id | code | valid_from | valid_to |
+----+------+------------+------------+
| 1A | ABC | 2014-12-01 | 2019-09-16 |
| 1A | DEF | 2015-03-31 | 2019-06-29 |
| 1B | MNO | 2015-04-22 | 2015-06-20 |
| 1B | PQR | 2015-06-21 | 2015-06-30 |
| 1B | STU | 2015-07-01 | 2015-08-31 |
| 1B | VWX | 2015-09-01 | 9999-12-31 |
+----+------+------------+------------+
It produces what I expect for id=1B but not for id=1A. Indeed, as the code=ABC appears once in the beginning and twice at the end, the result table put valid_from=2014-12-01.
What I would like is the valid_from for code=ABC to be 2019-06-30. In other words, I would like SAS to "forget" the first occurence of the code if there is an (or multiple) other code in between. The final table would look like this:
+----+------+------------+------------+
| id | code | valid_from | valid_to |
+----+------+------------+------------+
| 1A | DEF | 2015-03-31 | 2019-06-29 |
| 1A | ABC | 2019-06-30 | 2019-09-16 |
| 1B | MNO | 2015-04-22 | 2015-06-20 |
| 1B | PQR | 2015-06-21 | 2015-06-30 |
| 1B | STU | 2015-07-01 | 2015-08-31 |
| 1B | VWX | 2015-09-01 | 9999-12-31 |
+----+------+------------+------------+
Aucun commentaire:
Enregistrer un commentaire