This is part of one of the automation. Users will give me one query, and I will need to break that query into all possible queries using where clause column /conditions to verify which query throwing 0 records. Ex: Query1: select * from emp where sal>5000; Query2: select * from emp where sal>5000 and deptno=30; Once I make the diff queries based on the input query, will submit queries to find whether all queries populating data or not. As part of this, first I want to read the query into a single column in SAS Dataset. ... View more

When I tried to insert data into oracle table I can values like 14JUN13. Loading date values with 10 years back. Please suggest if I need to use any formats. ... View more

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

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. ... View more

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()'). ... View more

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. ... View more

I think you are right, need to verify the sas table columns and oracle table columns. And Yes src_dt is not null in my case. I will revisit my code and will get back you. ... View more

I even tried below steps. Proc sql; Create table A as Select "28MAR2023" As src_dt from tmp; Proc sql; Create table B as Select dhms(src_dt,0,0,0) as src_dt format=datetime20. from tmp; quit; Proc append data=B Base=db1.tbl(sasdatefmt=(src_dt=datetime20.)) ; Run; In this case also same issue, I am not able to resolve. ... View more