topic Re: select the last observation by id and date in SAS Programming

select the last observation by id and date

sasphd — Thu, 05 Oct 2023 18:43:08 GMT

hello,

I want to select the last observation by crsp_portno and REPORT_DT (is a date)

data first_by_group;
	set number_stocks ;
	by crsp_portno REPORT_DT;
 
	if  last.REPORT_DT then output;
run;

Re: select the last observation by id and date

PaigeMiller — Thu, 05 Oct 2023 19:38:45 GMT

if last.crsp_portno;

If you want the last observation for a given crsp_portno, then you use the above. I realize that my sentence is not what you said. You said "last observation by id and date", in which case the code you showed ought to work fine (and so, what is wrong with it?) Which brings up another point ... please do NOT show us your code and then not even bother to tell us what is wrong with it.

Re: select the last observation by id and date

sasphd — Thu, 05 Oct 2023 21:49:57 GMT

hello,

I want the last observation by date and id or my code give the last observation by id only

thanks a lot

Re: select the last observation by id and date

Reeza — Thu, 05 Oct 2023 22:09:41 GMT

Time for a data example then. Fake data is fine, show what the input looks like and what you expect.

FIRST/LAST will not handle multiple records per date for one thing. But typically these questions are selecting the correct first/last variables.

Re: select the last observation by id and date

PaigeMiller — Fri, 06 Oct 2023 14:32:05 GMT

@sasphd wrote:

I want the last observation by date and id or my code give the last observation by id only

This doesn't make sense. Your code will give the last observation for each value of REPORT_DT. Please show us a portion of your actual data, provided as working SAS data step code (examples and instructions).

Re: select the last observation by id and date

mkeintz — Sat, 07 Oct 2023 04:09:20 GMT

Your code will produce one observation for each crsp_portno*report_dt combination. So if some portfolios had multiple obs for a given report_dt, then you would get the last one of that set, for each report_dt.

data want;
	set number_stocks ;
	by crsp_portno REPORT_DT; 
	if  last.REPORT_DT then output;
run;

But as @PaigeMiller and @Reeza suspect (and so do I) you likely have one record for each crsp_portno*report_dt combination, and you want only the last one. i.e. the most recent date for each portfolio. If so, then @PaigeMiller's solution is what you want.

If this doesn't help, then please provide sample data, in the form of a working data step, and the expected output from that sample.