DATA Step, Macro, Functions and more

Need to retain seq number for same visit

Accepted Solution Solved
Reply
Regular Contributor
Posts: 194
Accepted Solution

Need to retain seq number for same visit

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 8) 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 8) 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 .  

Accepted Solutions
Highlighted
Solution
a week ago
Super User
Posts: 9,339

Re: Need to retain seq number for same visit


@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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Super User
Posts: 9,339

Re: Need to retain seq number for same visit

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
PROC Star
Posts: 1,133

Re: Need to retain seq number for same visit

Post your data in the form of a data step.

 

Makes it much easier to help you.

Super User
Super User
Posts: 8,987

Re: Need to retain seq number for same visit

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

 

Regular Contributor
Posts: 194

Re: Need to retain seq number for same visit

[ Edited ]

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

Super User
Posts: 9,339

Re: Need to retain seq number for same visit

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Regular Contributor
Posts: 194

Re: Need to retain seq number for same visit

Posted in reply to KurtBremser

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

Super User
Posts: 9,339

Re: Need to retain seq number for same visit

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Regular Contributor
Posts: 194

Re: Need to retain seq number for same visit

Posted in reply to KurtBremser

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

Highlighted
Solution
a week ago
Super User
Posts: 9,339

Re: Need to retain seq number for same visit


@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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 130 views
  • 4 likes
  • 4 in conversation