topic Re: Selecting records with latest date in SAS Programming

Selecting records with latest date

kmardinian — Thu, 04 Oct 2018 17:05:41 GMT

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!

Re: Selecting records with latest date

Reeza — Thu, 04 Oct 2018 17:20:31 GMT

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!

Re: Selecting records with latest date

kmardinian — Thu, 04 Oct 2018 17:22:54 GMT

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!

Re: Selecting records with latest date

Reeza — Thu, 04 Oct 2018 17:26:20 GMT

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.

Re: Selecting records with latest date

novinosrin — Thu, 04 Oct 2018 17:30:23 GMT

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!

Re: Selecting records with latest date

kmardinian — Thu, 04 Oct 2018 17:41:50 GMT

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

Thank you for your help!

Re: Selecting records with latest date

Andygray — Thu, 04 Oct 2018 17:46:50 GMT

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