Retain value for an id

mariko5797 — Mon, 01 Nov 2021 16:05:18 GMT

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.

Re: Retain value for an id

mariko5797 — Mon, 01 Nov 2021 16:10:21 GMT

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.

Re: Retain value for an id

Seadrago — Mon, 01 Nov 2021 17:09:23 GMT

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.

Re: Retain value for an id

Tom — Mon, 01 Nov 2021 17:39:51 GMT

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;

topic Retain value for an id in SAS Programming

Retain value for an id

Re: Retain value for an id

Re: Retain value for an id

Re: Retain value for an id