topic Re: Need to retain seq number for same visit in SAS Programming

Need to retain seq number for same visit

vraj1 — Tue, 17 Apr 2018 07:54:38 GMT

I have data like below and need to get seq retained if it is the Visit 1 (timeH) and seq is missing. in the blow case i need seq of studyNN12 as 1 for Visit 1(time H). Any help

usubj	dayn	day	value	visit	study	seq	date
HED101	0	Day 0	20.2	Visit 1 (Screening)	NN11	1	30-11-2017
HED101	28	Day 28	20.2	Visit 6 (End of Week 4)	NN11	34	12-01-2018
HED101	.		20.2		NN11	34
HED101	56	Day 56	20.2	Visit 10 (End of Week 😎	NN11	61	07-02-2018
HED101	84	Day 84	20.2	Visit 1 (timeH)	NN11	1	06-03-2018
HED101	84	Day 84	20.2	Visit 12 (measure)	NN11	76	07-03-2018
HED101	.		20.2		NN11	76
HED101	0	Day 0	20.2	Visit 6 (End of Week 4)	NN11	34	12-01-2018
HED101	28	Day 28	20.2	Visit 10 (End of Week 😎	NN11	61	07-02-2018
HED101	56	Day 56	20.2	Visit 1 (timeH)	NN11	1	06-03-2018
HED101	56	Day 56	20.2	Visit 12 (measure)	NN11	76	07-03-2018
HED101	.		20.2		NN11	76
HED101	0	Day 0	20	Visit 1 (timeH)	NN12	.

Re: Need to retain seq number for same visit

Kurt_Bremser — Tue, 17 Apr 2018 08:02:06 GMT

Please follow the guidelines for posting data in a data step. As soon as I have usable data, I can supply some code.

See my footnote.

Re: Need to retain seq number for same visit

PeterClemmensen — Tue, 17 Apr 2018 08:03:45 GMT

Post your data in the form of a data step.

Makes it much easier to help you.

Re: Need to retain seq number for same visit

RW9 — Tue, 17 Apr 2018 08:05:51 GMT

As mentioned several times, we are not here to type in test data for you. Provide test data in the form of a datastep and show what you want out. From your text:

1) Sort the data

2) Retain a new variable called tmp_seq

3) If first.visit then set as ...

4) Else if seq is missing then use tmp_seq, else use seq

Re: Need to retain seq number for same visit

vraj1 — Tue, 17 Apr 2018 09:11:57 GMT

Sorry this was the data i have and need seq to have value retained if it is missing. It needs to be replaced from visit1 value. in this case it should be 1.

data WORK.MYDATA;
  infile datalines dsd truncover;
  input F1:$6. F2:32. F3:$6. F4:$5. F5:$24. F6:$5. F7:32. F8:DATE9.;
datalines4;
usubj,,day,value,visit,study,,
HED101,0,Day 0,20.2,Visit 1 (Screening),NN11,1,30NOV2017
HED101,28,Day 28,20.2,Visit 6 (End of Week 4),NN11,34,12JAN2018
HED101,,,20.2,,NN11,34,
HED101,56,Day 56,20.2,Visit 10 (End of Week 8),NN11,61,07FEB2018
HED101,84,Day 84,20.2,Visit 1 (timeH),NN11,1,06MAR2018
HED101,84,Day 84,20.2,Visit 12 (measure),NN11,76,07MAR2018
HED101,,,20.2,,NN11,76,
HED101,0,Day 0,20.2,Visit 6 (End of Week 4),NN11,34,12JAN2018
HED101,28,Day 28,20.2,Visit 10 (End of Week 8),NN11,61,07FEB2018
HED101,56,Day 56,20.2,Visit 1 (timeH),NN11,1,06MAR2018
HED101,56,Day 56,20.2,Visit 12 (measure),NN11,76,07MAR2018
HED101,,,20.2,,NN11,76,
HED101,0,Day 0,20,Visit 1 (timeH),NN12,,
;;;;

Edit: no need to use an attachment for this rather short code; moved attachment into code window

Re: Need to retain seq number for same visit

Kurt_Bremser — Tue, 17 Apr 2018 09:25:22 GMT

I slightly changed your code, and added a data step. See if it does what you want:

data have;
  infile datalines dsd truncover;
  input usubj:$6. dayn day:$6. value visit:$24. study:$5. seq date:DATE9.;
  format date date9.;
datalines4;
HED101,0,Day 0,20.2,Visit 1 (Screening),NN11,1,30NOV2017
HED101,28,Day 28,20.2,Visit 6 (End of Week 4),NN11,34,12JAN2018
HED101,,,20.2,,NN11,34,
HED101,56,Day 56,20.2,Visit 10 (End of Week 8),NN11,61,07FEB2018
HED101,84,Day 84,20.2,Visit 1 (timeH),NN11,1,06MAR2018
HED101,84,Day 84,20.2,Visit 12 (measure),NN11,76,07MAR2018
HED101,,,20.2,,NN11,76,
HED101,0,Day 0,20.2,Visit 6 (End of Week 4),NN11,34,12JAN2018
HED101,28,Day 28,20.2,Visit 10 (End of Week 8),NN11,61,07FEB2018
HED101,56,Day 56,20.2,Visit 1 (timeH),NN11,1,06MAR2018
HED101,56,Day 56,20.2,Visit 12 (measure),NN11,76,07MAR2018
HED101,,,20.2,,NN11,76,
HED101,0,Day 0,20,Visit 1 (timeH),NN12,,
;;;;
run;

data want;
set have;
if seq = . then seq = input(scan(visit,2),3.);
run;

Re: Need to retain seq number for same visit

vraj1 — Tue, 17 Apr 2018 10:02:50 GMT

Hi Kurt, This will not work as seq is not from visit and it will change as the data goes on and different from visit information

Re: Need to retain seq number for same visit

Kurt_Bremser — Tue, 17 Apr 2018 10:24:50 GMT

So, from WHERE should the missing value be replaced? State a clear, complete and correct rule.

Re: Need to retain seq number for same visit

vraj1 — Tue, 17 Apr 2018 10:27:35 GMT

Missing seq should be replaced from observation which has visit as visit1 and seq ne missing. That seq number should be retained to the seq number which has missing seq and visit as visit1

Re: Need to retain seq number for same visit

Kurt_Bremser — Tue, 17 Apr 2018 10:34:16 GMT

@vraj1 wrote:

Missing seq should be replaced from observation which has visit as visit1 and seq ne missing. That seq number should be retained to the seq number which has missing seq and visit as visit1

Then replace the second data step with

data want;
set have;
by usubj;
retain retseq;
if first.usubj then retseq = .;
if input(scan(visit,2),3.) = 1 /* Visit 1 */
then do;
  if seq ne .
  then retseq = seq;
  else seq = retseq;
end;
drop retseq;
run;