topic Re: Select last value in calendar year in SAS Programming

Select last value in calendar year

luch25 — Fri, 08 Apr 2022 16:59:37 GMT

Hello,

I have two questions, but I think they are related in concept and need a similar solution.

1. I need to select the last value in a calendar each for each individual and output it into a new dataset.

2. I need to select the lowest value in a single day and output that into a new dataset.

I know how to select the last possible value per id, but I'm not sure what to do when it depends on an id and time period, or how to strip that time period from the date given.

I have the following example dataset below. I'm so sorry, but i can't get the input of the date value correct. Please let me know how I can fix for the future.

data have;
input studyid date value;
format date datetime22.3;
cards;
1 13JAN2011:07:53:00.000 19
1 20JUL2011:07:30:00.000 24
1 07AUG2014:12:09:06.000 8
1 07AUG2014:15:08.08.000 4
1 09OCT2014:13:42:00.000 10
2 30DEC2004:16:05:00.000 18
2 30DEC2004:19.05:00.000 20
;

I would want the datasets to look like this:

1	2011	24
1	2014	10
2	2004	20

1	13JAN2011	19
1	20JUL2011	24
1	07AUG2014	4
1	09OCT2014	10
2	30DEC2004	18

Thank you so much!

Re: Select last value in calendar year

PaigeMiller — Fri, 08 Apr 2022 17:06:37 GMT

Here's the answer to #1. You can do relatively the same thing for your question #2, so I leave that as a homework assignment for you.

data have1;
    set have;
    year=year(datepart(date));
run;
data want;
    set have1;
    by studyid year;
    if last.year;
run;

Regarding the code you provided, please test your code first to make sure it works properly. Don't make us debug your code, that's something you should be doing.

Re: Select last value in calendar year

rudfaden — Fri, 08 Apr 2022 17:07:13 GMT

Try something like this. I am not able to test this wrigth now. But take it as insperation.

Sort by id and descending date. Output last date (which is the lowest value as you sorted descending).

proc sort data=have;
by studyid descending date;
run;

data want;
set have;
by studyid descending date;
dateNew=datepart(date);
if last.date then output;
run;

Something like this should work

Re: Select last value in calendar year

Kurt_Bremser — Fri, 08 Apr 2022 17:12:15 GMT

proc sql;
create table want as
  select
    studyid,
    year(datepart(date)) as year,
    value
  from have
  group by studyid, calculated year
  having date = max(date)
;
quit;

Untested, posted from my tablet.

Re: Select last value in calendar year

luch25 — Fri, 08 Apr 2022 19:41:06 GMT

Thank you, everyone! They all worked, and i was able to use the template to figure it out how to select the lowest value in a day as well. Appreciate the guidance

Re: Select last value in calendar year

average_joe — Fri, 08 Apr 2022 19:56:32 GMT

This creates the 2 datasets you requested.

data have;
input studyid timestamp datetime22.3 value;
date = datepart(timestamp);
year = year(date);
format timestamp datetime22. date date.;
cards;
1 13JAN2011:07:53:00.000 19
1 20JUL2011:07:30:00.000 24
1 07AUG2014:12:09:06.000 8
1 07AUG2014:15:08:08.000 4
1 09OCT2014:13:42:00.000 10
2 30DEC2004:16:05:00.000 18
2 30DEC2004:19:05:00.000 20
;
run;

proc sort data=have;
by studyid year;
run;

data want1;
set have;
by studyid year;
if last.year;
keep studyid year value;
run;

proc sort data=have;
by studyid date value;
run;

data want2;
set have;
by studyid date;
if first.date;
keep studyid date value;
run;