want to convert date

[anirudhs]

Hi need help in converting the date :

today() =20210403 or today()=202104i.e(yyyymm)

as in some database the data is in 20210403 and variable type as numeric.

so for fetching date using a passthrough we have to pass manually done in below code

how can i automate the passing of load_date variable.

options compress=yes;
proc sql ;
connect to odbc(
datasrc='asdf'
user='sdfgth'
password='ddddxxxxxxxx'
);
create table ADVICE_raw as
select * from connection to odbc
(
Select * from wdf.advice_vw
where load_date >=20210301;
);

disconnect from odbc;
quit;

so in the above code load_date>=20210301

[Kurt_Bremser]

First of all, you need to know the notation of a date literal for the DBMS you send the code to. Then you can build the cast directive with the proper format.

But with such a simple WHERE, I would forego the explicit pass-through and let SAS handle the conversion in an implicit one (define a ODBC LIBNAME and pull the data from that).

[anirudhs]

Hi Kurt,

Actually i tried that but we are fetching this data from the hadoop DB and there is some issue with the driver so , it was suggested to use the pass-through.
As i find datastep more easy.

[Patrick]

Below should work.

options compress=yes;
proc sql;
  connect to odbc(
    datasrc='asdf'
    user='sdfgth'
    password='ddddxxxxxxxx'
    );
  create table ADVICE_raw as
    select * from connection to odbc
      (
    Select * from wdf.advice_vw
      where load_date >=%sysfunc(today(),yymmddn8.);
      );
  disconnect from odbc;
quit;

[Tom]

So you are saying that in the remote database they are literally storing 03APR2021 as the number 20,210,403 ?

Or does the database have an actual concept of storing date but allows code to reference them with a string that looks like any integer instead?

If you just want to generate a string in YYYYMMDD style then you can use the YYMMDDN8. format.

For example you could build a macro variable named TODAY with the string for today's date by using %SYSFUNC() to call the TODAY() function and format the result with the YYMMDDN8. format.

%put today=%sysfunc(today(),yymmddn8.);

You could then reference that macro variable in place where you want that string of digits to appear in the code.

where load_date >= &today.

[anirudhs]

and how can i pass the yyyymm (202104) instead of the full date ??

[Tom · Accepted solution]

The YYMMN6. format will produce strings in YYYYMM style. 

%let thismonth=%sysfunc(today(),yymmn6.);

Or just use %SUBSTR() to take the first 6 characters from the 8 character string you generated with the full date.

%let thismonth=%substr(&today,1,6);