topic Re: Getting max from a date in proc sql in SAS Programming

Getting max from a date in proc sql

Jose7 — Tue, 25 May 2021 02:19:40 GMT

Hi experts, I have this code:

PROC SQL;
CREATE TABLE my_table AS
SELECT DISTINCT
MAX(T1.sequential) AS num,
MAX(T1.date1) AS pay_date format date9.,
T1.sec AS sequential

FROM table_one T1, table_two T2
WHERE T1.number_A = T2.number_A
GROUP BY T1.name;
QUIT;

and i obtain this : ****** in the max of the date, the format (date9.) does not work

num pay_date sequential

3375414 ********* 24025521

Re: Getting max from a date in proc sql

Jose7 — Tue, 25 May 2021 02:21:28 GMT

Sorry the group by is T1.sec

Re: Getting max from a date in proc sql

Tom — Tue, 25 May 2021 04:30:32 GMT

A lot of people will label a variable that contains DATETIME values with a name like DATE1.

Try either using a DATETIME format on the MAX() value.

Or use the DATEPART() function to convert the datetime values (number of seconds) into date values (number of days) and then you can use the DATE format to display the values in a human recognizable string.

Re: Getting max from a date in proc sql

ballardw — Tue, 25 May 2021 04:39:50 GMT

A display of ******* typically means that the value of the variable exceeds what the format is designed to work with.

What is the format of the variable Date1 in Table_one? If you see something like BEST12 then your "date" likely is just a number that you have thought was a "date".

Run Proc contents on Table_one and share the result.

Here is an example of what I think might be going on since you have not shared any actual value:

data example;
    date = 20200715; /* you might think this is 15Jul2020*/
   put date=  date9.;
run;

/* which the log will show */
137  data example;
138      date = 20200715; /* you might think this is 15Jul2020*/
139     put date=  date9.;
140  run;

date=*********

The 9 asterisks are because the format is 9 characters wide.

And how to get an actual date from that sort of number:

data example2;
   date = 20200715;
   actualdate = input(put(date,f8. -L),yymmdd10.);
   put actualdate date9.;
run;
/* which in the log shows*/
142  data example2;
143     date = 20200715;
144     actualdate = input(put(date,f8. -L),yymmdd10.);
145     put actualdate= date9.;
146  run;

actualdate=15JUL2020

SAS date values are the number of days since 1 Jan 1960. So the SAS date value in the example2 is 22111. If you have values like 20200715 you exceed the number of days SAS has defined formats and values for dates. The largest date SAS supports is 31 December 20,000 (yes year twentythousand) which is a number of 6589335. So any number larger than that is an invalid "date" as far as SAS is concerned, as is any date prior to 1582.

https://communities.sas.com/t5/SAS-Communities-Library/Working-with-Dates-and-Times-in-SAS-Tutorial/ta-p/424354 has a PDF with much information about dates.