I am trying to run the following code to keep only the last 'CreateDate' for each 'FileNo' however it is returning a blank dataset with the headers only.
date Dates_1;
set Dates;
If Last.createdate then output;
by FileNo;
Run;
Any assistance would be appreciated??
Thanks
I guess you wanted this:
proc sort data=Dates;
by FileNo createdate;
run;
data Dates_1;
set Dates;
by FileNo;
if last.FileNo;
run;
which will give you the latest createdate for any FileNo.
If you take a look at the log of your first try, you will get a clue where your problem is:
NOTE: Variable 'last.createdate'n is uninitialized.
A first. and last. pseudovariable is created only for those columns named in a by statement.
without the by statement first. or last. will not work, simple fix:
date Dates_1;
set Dates;
by createdate; /*add this line, must be sorted by createdate*/
If Last.createdate then output;
Run;
I will try this now.
I assumed i would have to have it sorted by FileNo as i wanted to keep the last CreateDate for each FileNo.
I will post the outcome, although i am sure it will now work if it was a simple error as advised.
Thanks
Ah, yes, I think you are trying to get the last file date for each file yes? Mark Johnson has provided the answer, however that will just give one record for the data. First you want to sort your data by fileno and create date, then:
date Dates_1;
set Dates;
by fileno createdate;
If Last.fileno then output;
Run;
This will give one row per file with the last create date.
You can do it in one step in SQL;
proc sql;
create table WANT as
select FILENO,
CREATEDATE
from HAVE
group by FILENO
having CREATEDATE=max(CREATEDATE);
quit;
I guess you wanted this:
proc sort data=Dates;
by FileNo createdate;
run;
data Dates_1;
set Dates;
by FileNo;
if last.FileNo;
run;
which will give you the latest createdate for any FileNo.
If you take a look at the log of your first try, you will get a clue where your problem is:
NOTE: Variable 'last.createdate'n is uninitialized.
A first. and last. pseudovariable is created only for those columns named in a by statement.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.