BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
vraj1
Quartz | Level 8

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 .  
1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

@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;

View solution in original post

9 REPLIES 9
PeterClemmensen
Tourmaline | Level 20

Post your data in the form of a data step.

 

Makes it much easier to help you.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

 

vraj1
Quartz | Level 8

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

Kurt_Bremser
Super User

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;
vraj1
Quartz | Level 8

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

vraj1
Quartz | Level 8

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

Kurt_Bremser
Super User

@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;

sas-innovate-2024.png

Today is the last day to save with the early bird rate! Register today for just $695 - $100 off the standard rate.

 

Plus, pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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