I want to repeat the flagging variable down the column for a given ID.
data have;
input usubjid $5. visit $10. stdtc $ agendtc $;
cards;
VMP01 MOCK Day1 17SEP20:06:40 17SEP20:07:42
VMP01 MOCK Day2 18SEP20:12:08 18SEP20:13:01
VMP01 CHMI1 Day1 20SEP20:14:09 20SEP20:14:45
VMP01 CHMI1 Day2 21SEP20:19:23 21SEP20:20:13
VMP01 CHMI1 Day3 22SEP20:09:35 22SEP20:10:56
VMP01 CHMI2 Day1 23SEP20:18:12 23SEP20:18:57
VMP02 MOCK Day1 05MAY19:12:42 05MAY19:13:59
VMP02 CHMI1 Day1 01JUN19:06:14 01JUN19:07:21
VMP02 CHMI1 Day2 02JUN19:09:42 02JUN19:10:36
VMP02 CHMI2 Day1 06JUN19:10:34 06JUN19:11:47
VMP02 CHMI2 Day2 07JUN19:09:57 07JUN19:11:03
VMP02 CHMI2 Day3 08JUN19:10:12 08JUN19:11:13
VMP02 CHMI4 Day1 24JUN19:10:54 24JUN19:12:07
VMP03 CHMI1 Day1 16AUG18:13:38 16AUG18:14:35
VMP03 CHMI2 Day1 03SEP18:20:12 03SEP18:20:59
VMP03 CHMI3 Day1 11OCT18:17:31 11OCT18:18:41
VMP03 CHMI3 Day2 12OCT18:13:00 12OCT18:14:01
VMP04 MOCK Day1 09DEC18:12:08 09DEC18:13:12
VMP04 MOCK Day2 10DEC18:15:50 10DEC18:16:27
VMP05 CHMI2 Day1 31MAY19:08:51 31MAY19:10:06
VMP05 CHMI4 Day1 27MAR20:19:20 27MAR20:20:13
VMP05 CHMI4 Day2 28MAR20:17:02 28MAR20:17:58
;
run;
I want the flags to be like so:
data want;
input usubjid $5. visit $10. stdtc $ agendtc $ aperiod aperiod0 aperiod1 aperiod2 aperiod3 aperiod4;
cards;
VMP01 MOCK Day1 17SEP20:06:40 17SEP20:07:42 0 1 1 1 . .
VMP01 MOCK Day2 18SEP20:12:08 18SEP20:13:01 0 1 1 1 . .
VMP01 CHMI1 Day1 20SEP20:14:09 20SEP20:14:45 1 1 1 1 . .
VMP01 CHMI1 Day2 21SEP20:19:23 21SEP20:20:13 1 1 1 1 . .
VMP01 CHMI1 Day3 22SEP20:09:35 22SEP20:10:56 1 1 1 1 . .
VMP01 CHMI2 Day1 23SEP20:18:12 23SEP20:18:57 2 1 1 1 . .
VMP02 MOCK Day1 05MAY19:12:42 05MAY19:13:59 1 1 1 1 . 1
VMP02 CHMI1 Day1 01JUN19:06:14 01JUN19:07:21 1 1 1 1 . 1
VMP02 CHMI1 Day2 02JUN19:09:42 02JUN19:10:36 1 1 1 1 . 1
VMP02 CHMI2 Day1 06JUN19:10:34 06JUN19:11:47 2 1 1 1 . 1
VMP02 CHMI2 Day2 07JUN19:09:57 07JUN19:11:03 2 1 1 1 . 1
VMP02 CHMI2 Day3 08JUN19:10:12 08JUN19:11:13 1 1 1 1 . 1
VMP02 CHMI4 Day1 24JUN19:10:54 24JUN19:12:07 4 1 1 1 . 1
VMP03 CHMI1 Day1 16AUG18:13:38 16AUG18:14:35 1 . 1 1 1 .
VMP03 CHMI2 Day1 03SEP18:20:12 03SEP18:20:59 2 . 1 1 1 .
VMP03 CHMI3 Day1 11OCT18:17:31 11OCT18:18:41 3 . 1 1 1 .
VMP03 CHMI3 Day2 12OCT18:13:00 12OCT18:14:01 3 . 1 1 1 .
VMP04 MOCK Day1 09DEC18:12:08 09DEC18:13:12 0 1 . . . .
VMP04 MOCK Day2 10DEC18:15:50 10DEC18:16:27 0 1 . . . .
VMP05 CHMI2 Day1 31MAY19:08:51 31MAY19:10:06 2 1 . 1 . 1
VMP05 CHMI4 Day1 27MAR20:19:20 27MAR20:20:13 4 1 . 1 . 1
VMP05 CHMI4 Day2 28MAR20:17:02 28MAR20:17:58 4 1 . 1 . 1
;
run;
NOTE: As long as the dates are in order, it doesn't really matter if the value is copied upward. It just needs to retain 1 for proceeding visits. This will be used for if-then statement later on.
My attempt to get there is:
data expose_;
set have;
retain aperiod0 aperiod1 aperiod2 aperiod3 aperiod4;
by usubjid;
/*convert character to date variable*/
stdtc = input(substr(agstdtc,1,16), e8601dt19.);
endtc = input(substr(agendtc,1,16), e8601dt19.);
/*create period variable for categorizing*/
if findw(visit, 'MOCK') > 0 then do; aperiod = 0; aperiod0 = 1; end;
else if findw(visit, 'CHMI1') > 0 then do; aperiod = 1; aperiod1 = 1; end;
else if findw(visit, 'CHMI2') > 0 then do; aperiod = 2; aperiod2 = 1; end;
else if findw(visit, 'CHMI3') > 0 then do; aperiod = 3; aperiod3 = 1; end;
else if findw(visit, 'CHMI4') > 0 then do; aperiod = 4; aperiod4 = 1; end;
format stdtc endtc datetime.;
drop agstdtc agendtc;
run; /*57 obs*/
It is just marking 1 from when the character string first appears all the way down. I want it to only go down till the next ID.
EDIT: I am not the most familiar with manually inputting data, so the input statement needs some tweaking to come out properly. But "MOCK Day1" and so forth is for VISIT variable.
I apologize for this inconvenience.
Hi, I think I've got a partial solution.
with retain statements you'll usually need a proc sort statement as well as a if first. for it to perform the way you want it to
you can do something similar with the other aperiods you need.
I don't understand the question.
In general if you want the value to stay the same until changed use the RETAIN statement.
In general if you want the values to reset when you start a new block of data use FIRST. variable.
data want;
set have;
by usubjid;
retain aperiod aperiod0-aperiod4;
if first.usubjid then call missing(of aperiod aperiod0-aperiod4);
... rest of your logic here....
run;
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.