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!
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.
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!
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!
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.
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!
Thank you, both of those ways worked. Too bad I cannot pick two answers 😕
Thank you for your help!
@kmardinian You could click "Like" to acknowledge the help if you want while having chosen another answer as accepted solution. I do this way
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.