BookmarkSubscribeRSS Feed
sasphd
Lapis Lazuli | Level 10

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;
5 REPLIES 5
PaigeMiller
Diamond | Level 26
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.

--
Paige Miller
sasphd
Lapis Lazuli | Level 10

hello, 

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

thanks a lot

Reeza
Super User
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.
PaigeMiller
Diamond | Level 26

@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). 

--
Paige Miller
mkeintz
PROC Star

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.

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 673 views
  • 5 likes
  • 4 in conversation