Convert STRING today() function to SAS Date

[Banu]

Hi Team,

I have an oracle table with two columns start_dt and end_dt which holding the data like today()-2 and today() in VARCHAR2(50) datatype.

Could you please help me to convert today()-2 and today() string values into SAS dates please. 

Thank you.

[yabwon]

How does some example data in that columns look like?

Is it "YYYY-MM-DD" ?

[Banu]

I have created an oracle table as below.
create table ref_data(start_dt,end_dt) values ('today()-2','today()');

Now I want to convert 'today()' (STRING) into SAS date value.

[Tom]

You need explain it more detail what you have because if you only have those two strings just type them into a program.

If you have code in data then you need to use some type of code generation to be able to run the code.  That is you need to use the data to write the code.

So if you have this dataset:

data have;
  start_dt ='today()-2';
  end_dt = 'todya()';
run;

And you want to get this dataset ;

data want;
  start_dt = today()-2;
  end_dt = today();
  format start_dt end_dt date9.;
run;

Then you could a program like this to read HAVE and generate the code to create WANT.

filename code temp;
data _null_;
  set have;
  file code;
  put 'start_dt=' start_dt ';'  ;
  put 'end_dt=' end_dt ';' ;
run;

data want;
%include code / source2;
  format start_dt end_dt date9.;
run;

[Banu]

Thanks for your response. Below have given more details. Please chekc and suggest.

Consider I have a SAS Temporary table which holds table name, schema name , load_dt, start_dt,end_dt like below. And I have similar data with 200 records in SAS temp Dataset..

SALES,SALES_DW,23147,'TODAY()-2','TODAY()' --> Sample Data in reference Table.

My SAS code should verify like below.

SELECT TABLE_NAME, SCHEMA_NAME,CASE WHEN start_dt <= table_load_dt <= end_dt then 'Y' else 'N' end as DATA_EXISTS
FROM reference_table;

Here I am getting an error as datatype mismatch since table_load_dt in SAS date Value and START_DT and END_DT's are char datatype ('today()').

[yabwon]

It's a bit over kill, but does this:

/* assumption: today() returns May 17th, 2023 */
data have;
infile cards dsd;
input (t s) (: $20.) dt (sd ed) (: $10.);
format dt yymmdd10.;
cards;
SALES,SALES_DW,23146,TODAY()-2,TODAY()
CUSTOMERS,SALES_DW,23148,TODAY()-22,TODAY()+16
;
run;

proc print data=have;
run;

data want;
  set have;


  dsStr=cat(
    "have(rename=(dt=_N_) where=(s='",strip(s),
    "' and t='",strip(t),"' ",
    "  and (",sd,") <=_N_<= (",ed,")",
    "))"
  );
  drop dsStr;
  declare hash H(dataset:dsStr);
  H.defineKey("_N_");
  H.defineDone();

  if H.NUM_ITEMS >0 
    then DATA_EXISTS="Yes";
    else DATA_EXISTS="No";
  H.delete();

run;

proc print data=want;
run;

help?

Bart

[Tom]

You have left out details that make it impossible to give you a working solution.

What is the name of the dataset that has the character variables START_DT and END_DT? 

Do all of the observations have the same values of START_DT and END_DT?

You can check by running PROC FREQ:

proc freq;
  tables start_dt*end_dt / list missing;
run;

Is that same dataset as the one named REFERENCE_TABLE you are querying later?

Where is this TABLE_LOAD_DT variable?  Is it the same variable as the LOAD_DT variable in the dataset with the START_DT and END_DT character strings? 

Does this DT suffix on your variable names stand for DaTe values (number of days) or DateTime values (number of seconds)?

The key to getting code generation to work ( ie to creating a macro) is you need to know what code you want to generate.

Show me the code that works to populate DATA_EXISTS variable for one or two of the records in your temporary table and the results you get when you run it.

[Banu]

Extremely sorry for the late response.  Below have trying my level best to show the scenario.

Table1: INPUT TABLE [ORACLE TABLE]

db_nm(VARCHAR2(50),tbl_nm(VARCHAR2(50),load_col(VARCHAR2(50),chk_start(VARCHAR2(50),chk_end(VARCHAR2(50)

store,customer,load_dt,today()-5,today()

store,product,load_dt,today()-10,today()

I have total 250 source tables list to verify.

Table2: OUTPUT TABLE [ SAS Dataset ]

db_nm,tbl_nm,data_availability_check

'store','customer','Y'

'store','product','Y'

Need to write SAS code in below format.

1. I managed to get the MAX(LOAD_COL)=LATEST_DT value in datetime20. format.

2. Now trying in below way.

    data table2;

     set table1;

     if chk_start >= LATEST_DT and chk_end <= LATEST_DT then data_availability_check='Y';

     else data_availability_check='N';

    run;

Error: CHK_START,CHK_END column values should be Numeric, but it hold Character value.

Note: TABLE1 is in oracle database (ALL COLUMNS are VARCHAR2(50).

            TABLE2 is in SAS (Dataset).

Also requirement here is , It's a data check program which verifies given database name and table names should have latest data i.e maximum load date should be between chk_start and chk_end dates, then only we make data flag as 'Y' else 'N'. I hope its helpful. Please help me to convert  'today()' string value into SAS date value using proc sql or datastep, so that I will convert into datetime20. format.

[Tom]

You are still not explaining a step here.

So you have this metadata in an Oracle table which seems to be used to define the CRITERIA to use for testing whether a DATE is within a range.

data table1 ;
  length db_nm tbl_nm load_col chk_start chk_end $50;
  input db_nm -- chk_end;
cards;
store customer load_dt today()-5  today()
store product  load_dt today()-10 today()
;

I am interpreting the first row to mean you want to check if LOAD_DT in STORE.CUSTOMER is between today and 5 days ago.

From your comments it sounds like you actually want to test if the MAX() of LOAD_DT is within that window so that for each variable you get only one answer, (Y/N or T/F or 1/0).

So to do that for STORE.CUSTOMER you might want to do something like

select max(load_dt) as max_load_dt from store.customer

Which you could then wrap into something that tests its value

select case when max_load_dt between today()-5 and today() then 'Y' else 'N' end as ANSWER from 
(select max(load_dt) as max_load_dt from store.customer)
;

If your LOAD_DT variable is a DATETIME variable instead of DATE variable then you cannot compare its value directly to the DATE value generated by the TODAY() function.  It is in the wrong units (seconds instead of days).  You can use the DATEPART() function to convert it to a DATE value instead.  

select case when datepart(max_load_dt) between today()-5 and today() then 'Y' else 'N' end as ANSWER from 
(select max(load_dt) as max_load_dt from store.customer)
;

As I explained before you cannot just compare LOAD_DT (or MAX_LOAD_DT) to the character strings you have in your metadata dataset (TABLE1).  You have to use the strings in TABLE1 to generate some CODE like the example above instead.

[Banu]

Thank you so much for your reply.

As you mentioned the code in previous comment, it won't work since today() -5 and today() are in String variable and we are not using direct today() functions since we need to handle dynamically. I am writing query using variables not hard coded values. If I use direct variables SAS is not considering today()-5 and today() as SAS functions, still considering as strings values and throwing check_start and end variables are not numeric.
Please Imagine, check_start and check_end values are in oracle table varchar2 datatype and we need to use direct variable names to check maximum load date(timestamp) in between check_start and check_end date windows in SAS proc sql. My request is how to can we make SAS to understand check_start and check_end values as Functions and not Strings using below query.

select case when datepart(max_load_dt) between check_start and check_end then 'Y' else 'N' end as ANSWER from
(select max(load_dt) as max_load_dt from store.customer).
;

[Tom]

Use the data to WRITE the code.  For example by using a PUT statement.

For example this data step will write one INSERT statement for each observation in your 

filename code temp;
data _null_;
  file code ;
  set table1 ;
  put 'insert into TABLE2 (db_nm,tbl_nm,data_availability_check)'
   /  'select ' db_nm :$quote. 
   /  '     , ' tbl_nm :$quote. 
   /  '     ,  case when datepart(max_load_dt) between ' check_start 'and ' check_end
        "then 'Y' else 'N' end"
   /  'from (select max(' load_col ') as max_load_dt from ' db_nm +(-1) '.' tbl_nm ')'
  ;
run;

You can then LOOK at the text file and make sure the generated code is correct.  Copy the first statement from the file and run it to see if it works to get the status of that table.  If not then modify the PUT statement so it writes the correct code.  For example if DB_NM and TBL_NM mean the names of an Oracle SCHEMA and a TABLE or VIEW in that schema then you probably need to modify the select clause to use explicit pass thru SQL instead of the above that is assuming that DB_NM has a SAS LIBREF and TBL_NM has the name of SAS dataset or view.

Once you get it to generate the right code then just use %INCLUDE to run it.

proc sql;
%include code / source2;
quit;