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