Selecting records with latest date

[kmardinian]

Hi, I am working with a dataset that looks like this:

ID          Time                   CScore     Visit   

1       2016-07-14             150            2        

1       2016-07-20             180            2

1       2016-08-02             120            3

2       2017-01-02             109            2

2       2017-01-05             110            3

3      2016-05-10              109            2

3      2016-05-12              110            2

3      2016-05-20              120            3

Visit 2 is considered baseline, I would like to choose the CScore value that was collected at the later time (variable Time). I had come up with this line of code so far:

proc sql;
create table t1_1 as
select *
from t1
group by CScore
having time=max(time);
quit;

But I cannot seem to find a way to incorporate the fact that I want it to only remove the earlier CScore when visit=2 for each subject.

Thank you, any help/advice is much appreciated!

[Reeza]

Use a data step with BY group processing. From the example data there, what output are you expecting?

---

@kmardinian wrote:

Hi, I am working with a dataset that looks like this:

ID          Time                   CScore     Visit   

1       2016-07-14             150            2        

1       2016-07-20             180            2

1       2016-08-02             120            3

2       2017-01-02             109            2

2       2017-01-05             110            3

3      2016-05-10              109            2

3      2016-05-12              110            2

3      2016-05-20              120            3

Visit 2 is considered baseline, I would like to choose the CScore value that was collected at the later time (variable Time). I had come up with this line of code so far:

proc sql;
create table t1_1 as
select *
from t1
group by CScore
having time=max(time);
quit;

But I cannot seem to find a way to incorporate the fact that I want it to only remove the earlier CScore when visit=2 for each subject.

Thank you, any help/advice is much appreciated!

---

[kmardinian]

The output I would like would look like this:

ID          Time                   CScore     Visit          

1       2016-07-20             180            2

1       2016-08-02             120            3

2       2017-01-02             109            2

2       2017-01-05             110            3

3      2016-05-12              110            2

3      2016-05-20              120            3

How would I go about doing that with a data step? Thank you!

[Reeza · Accepted solution]

proc sort data=have;
by id visit time;
run;

data want;
set have;
by id visit time;
if last.visit;
run;

First ensure your data is sorted and then take the last record for each visit using the LAST.<variableName> to identify the last record.

[novinosrin]

simple logical correction:

data t1;
input ID          Time    :yymmdd10.               CScore     Visit   ;
format  Time    yymmdd10.;
cards;
1       2016-07-14             150            2        
1       2016-07-20             180            2
1       2016-08-02             120            3
2       2017-01-02             109            2
2       2017-01-05             110            3
3      2016-05-10              109            2
3      2016-05-12              110            2
3      2016-05-20              120            3
;

 

proc sql;
create table t1_1 as
select *
from t1
group by id, visit
having time=max(time);
quit;

Btw, good effort though. We appreciate it!

[kmardinian]

Thank you, both of those ways worked. Too bad I cannot pick two answers 😕

Thank you for your help!

[Andygray]

@kmardinian You could click "Like" to acknowledge the help if you want while having chosen another answer as accepted solution. I do this way