SAS Datetime 22.3 to yymmddn8

Question

I am using SAS to bring over a table that sits in a Microsoft SQL Server and and dump it into Hadoop. The problem I am facing is that the data variable: INSURANCE_DATE.Week_Start_Date is a Datetime 22.3, but needs to be converted into yymmddn8. I am bringing 3 years of data over, the entry code piece:

%let three_year_start=%sysfunc(putn(%sysfunc ( intnx(year,%sysfunc(today()), -3)), yymmddn8.)); /* */ %let three_year_end=%sysfunc(putn(%sysfunc(today()), yymmddn8.)); /is today/

works fine, but I am not sure how to format INSURANCE_DATE.Week_Start_Date properly so that my where clause works.

    %let three_year_start=%sysfunc(putn(%sysfunc (                 
    intnx(year,%sysfunc(today()), -3)), yymmddn8.)); /* */
    %let three_year_end=%sysfunc(putn(%sysfunc(today()), yymmddn8.)); /*is 
    today*/
    proc SQL;

      create table BA_INS as

    SELECT  
    format (INSURANCE_DATE.Week_Start_Date) yymmdd10.) as Week_Start_Date

    FROM
      dbo.DIM_INSURANCE_DATE
    WHERE
    (dbo.DIM_INSURANCE_DATE.Week_Start_Date  
    between &three_year_start. and  &three_year_end.);
    quit;

I need a solution as how to convert that datatime 22.3 format into yymmdd10. within the proc sql query

Answer 1

If the source value pulled from SQL Server is, in SAS, presenting itself SAS datetime value, and you want the target value to be pushed to Hadoop from SAS to be as from a SAS date value, you can use the datepart function in SAS.

proc sql;
  create table HADOOP.TARGET_TABLE as
  select
  …
  , datepart(Week_Start_Date) as Week_Start_Date format=date9.
  …
  from
    SQLSRV.SOURCE_TABLE
  … 
  ;

I don't think the actual date format is important, only that it is one of the SAS date formats. The SAS/Connect engine will examine the SAS column going to target, see the date format and automatically make any tweaks needed to insert the SAS date value as a date value in the target system.

Answer 2

use datepart to take format and then apply format

SELECT  
datepart(INSURANCE_DATE.Week_Start_Date) format= yymmdd10. as Week_Start_Date

Answer 3

Not sure what you plan on using the macro variables for, but your are using an extra %SYSFUNC() call. You do not need to call PUTN() to format the value, %SYSFUNC() already will take a format specification.

%let three_year_start=%sysfunc(intnx(year,%sysfunc(today()),-3,b),yymmddn8.);
%let three_year_end=%sysfunc(today(), yymmddn8.);

So this will get you values like 20160101 and 20190123. Note to SAS if you did not enclose those in quotes they would be treated as the numbers 20,160,101 and 20,190,123 rather than anything to do with either date values or datetime values. And if you did enclose them in quotes then they would just be strings.

If you want to just generate a date value then don't bother to format the numbers. Then you will get values like THREE_YEAR_START=20454 and THREE_YEAR_END=21572 which represent the number of days since 1960. Then in your SQL code you could use those. But first you would need to convert your datetime value to a date value. Otherwise you would be comparing apples and oranges (seconds and days).

%let three_year_start=%sysfunc(intnx(year,%sysfunc(today()),-3,b));
%let three_year_end=%sysfunc(today());
...
where datepart(Week_Start_Date) between &three_year_start and &three_year_end

Or you could format your macro variables to look like values that the DATE (or DATETIME) informat could understand and use them as date (or datetime) literals by adding quotes and appropriate suffix letter(s).

DATE Literals

%let three_year_start=%sysfunc(intnx(year,%sysfunc(today()),-3,b),date9.);
%let three_year_end=%sysfunc(today(), date9.);
...
where datepart(Week_Start_Date) between "&three_year_start"d and "&three_year_end"d

DATETIME literals

%let three_year_start=%sysfunc(intnx(dtyear,%sysfunc(datetime()),-3,b),datetime19.);
%let three_year_end=%sysfunc(datetime(), datetime19.);
...
where Week_Start_Date between "&three_year_start"dt and "&three_year_end"dt