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

Have

Subject IDVisitScore
117961Visit10
117961Visit28
117961Visit34
117961Visit41
117961Visit51
117962Visit10
117962Visit26
117962Visit34
117962Visit49
117962Visit52
117963Visit10
117963Visit24
117963Visit32
117963Visit45
117963Visit53
117964Visit10
117964Visit23
117964Visit32
117964Visit48
117964Visit56

 

want 

Subject IDVisitScore
117962Visit10
117962Visit26
117962Visit34
117962Visit49
117962Visit52
117964Visit10
117964Visit23
117964Visit32
117964Visit48
117964Visit56

 

I want to have a subset of data by comparing the score of Visit 2 onward (I don't need to include Visit 1, since Visit 1 is baseline), where the difference between 2 cosecutive scores jump by greater than 4 points.

 

Thanks as always!! 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Shmuel
Garnet | Level 18

Asking for:

" I want records by "Subject", which means I want ALL the records of "those Subjects" whose 2 consective visits scores jumped gretaer than 4 points, when I do the comparsion, I don't need to consider the jump between the Visit 1 and Visit 2, I only need to consider the jump (increase) between visit 2 to visit 3, visit 3 to visit 4, and visit 4 to visit 5, when I find that subject, I wnat all his(her) records in the output dataset." - try next code:

data subjects(keep=subject_ID);
set have;
  by subject_ID;
      if not first.subject_ID and dif(score) ge 4 then output;
run;
proc sql;
   create table want as
   select * from have 
   where subject_ID in 
      (select distinct subject_ID form subjects)
; quit;

In case of any issue please post the log and describe the issue you have.

 

Pay attention:

- DIF function computes the difference between current and previous value of a variable.

View solution in original post

10 REPLIES 10
Reeza
Super User
So you want the highlighted records only?
zimcom
Pyrite | Level 9

I want every records of those subject(s) who has a jump of score greater than 4 

Reeza
Super User
Yes, but do you want both of those records, the first or the last? What happens if it's consecutive?
zimcom
Pyrite | Level 9
I want all the records of those subjects who has a jump of more than 4 from the previous visit
Tom
Super User Tom
Super User

You need to make two passes through the data. One to identify the subjects. One to select the data for those subjects.

You could use a method that goes by the name DOW loop.  Or double-DOW loop.  

data want ;
  do until (last.subjectid);
    set have;
    by subjectid vist;
    if dif(score) > 4 and not first.subjectid then found=1;
  end;
  do until(last.subjectid);
    set have;
    by subjectid;
    if found then output;
  end;
run;
Shmuel
Garnet | Level 18

Why did you not include subject_id=117961 visit=visit2 sore=0 in the wanted results

where the difference to previous visit is 8-0=8 which is greater then 4 ?

 

and why you did include subject_id=117962 visit=visit3 score=4 where the difference to

previous visit is -2 which is less then 4 ?

 

Try next code:

data want;
set have;
  by subject_ID;
      if not first.subject_ID and dif(score) ge 4 then output;
run;
zimcom
Pyrite | Level 9
Why did you not include subject_id=117961 visit=visit2 sore=0 in the wanted results
where the difference to previous visit is 8-0=8 which is greater then 4 ?
[Because the Visit1 is baseline and I only want the jump from Visit 2 and onward.]

and why you did include subject_id=117962 visit=visit3 score=4 where the difference to
previous visit is -2 which is less then 4 ?
[I am only interested in the increase, not the decrease. the difference is more than 4 from the previous one ]
Shmuel
Garnet | Level 18

Within all subjects the first visit is "Visit1" so using "not first.subject_ID" omits it;

 

you asked for: "I don't need to include Visit 1, since Visit 1 is baseline" then why is

subject=117962 visit=visit1 score=0  is in the wanted output? and why 

subject=117964 visit=visit1 score=0 is in the wanted output? 

 

You posted as wanted output observations which do not fit your specifications.

zimcom
Pyrite | Level 9

you asked for: "I don't need to include Visit 1, since Visit 1 is baseline" then why is

subject=117962 visit=visit1 score=0  is in the wanted output? and why 

subject=117964 visit=visit1 score=0 is in the wanted output? 

 

I am really sorry fo rthe confusion. In the wanted output, I want records by "Subject", which means I want ALL the records of "those Subjects" whose 2 consective visits scores jumped gretaer than 4 points, when I do the comparsion, I don't need to consider the jump between the Visit 1 and Visit 2, I only need to consider the jump (increase) between visit 2 to visit 3, visit 3 to visit 4, and visit 4 to visit 5, when I find that subject, I wnat all his(her) records in the output dataset. I hope it is clear now.

Shmuel
Garnet | Level 18

Asking for:

" I want records by "Subject", which means I want ALL the records of "those Subjects" whose 2 consective visits scores jumped gretaer than 4 points, when I do the comparsion, I don't need to consider the jump between the Visit 1 and Visit 2, I only need to consider the jump (increase) between visit 2 to visit 3, visit 3 to visit 4, and visit 4 to visit 5, when I find that subject, I wnat all his(her) records in the output dataset." - try next code:

data subjects(keep=subject_ID);
set have;
  by subject_ID;
      if not first.subject_ID and dif(score) ge 4 then output;
run;
proc sql;
   create table want as
   select * from have 
   where subject_ID in 
      (select distinct subject_ID form subjects)
; quit;

In case of any issue please post the log and describe the issue you have.

 

Pay attention:

- DIF function computes the difference between current and previous value of a variable.

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 10 replies
  • 1924 views
  • 2 likes
  • 4 in conversation