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