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 | . |
@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;
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.
Post your data in the form of a data step.
Makes it much easier to help you.
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
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
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;
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
So, from WHERE should the missing value be replaced? State a clear, complete and correct rule.
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
@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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.