topic Re: Date format from SQL To SAS in New SAS User

Date format from SQL To SAS

Citrine10 — Thu, 03 Dec 2020 11:10:07 GMT

hi there

i pull data from a SQL environment into SAS. There is a date field in the table which changes to a 5 digit numeric value. How can I get the date to display as dd/mm/yyyy?

Re: Date format from SQL To SAS

Kurt_Bremser — Thu, 03 Dec 2020 11:17:25 GMT

Apply the DDMMYY10. format.

SAS dates are counts of days, with 1960-01-01 being day zero. That translates to 5-digit integers for most of the dates you will work with.

Re: Date format from SQL To SAS

Citrine10 — Thu, 03 Dec 2020 11:37:39 GMT

I see when I do a max on the date it gives the issue.

Re: Date format from SQL To SAS

PaigeMiller — Thu, 03 Dec 2020 11:51:56 GMT

@Citrine10 wrote:

I see when I do a max on the date it gives the issue.

You have to apply a date format to any variables that might be a date, including the one extracted from the database via PROC SQL and any other date variables you create via a function.

Re: Date format from SQL To SAS

Kurt_Bremser — Thu, 03 Dec 2020 12:20:39 GMT

@Citrine10 wrote:

hi

I see when I do a max on the date it gives the issue.

The result of a MAX function is the raw value, SQL does not automatically apply the format of the source variable.

See this:

data have;
input dateval yymmdd10.;
format dateval yymmdd10.;
datalines;
2020-05-02
2020-07-13
;

proc sql;
select max(dateval) as dateval
from have
;
select max(dateval) as dateval format=yymmdd10.
from have
;
quit;

Result:

    dateval
   --------
      22109
              

     dateval
  ----------
  2020-07-13