@Banu wrote: 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. Reading text from a file is trivial.    Making sense of it as SQL syntax is difficult.   One restriction is that character variables can only store 32K bytes.  So your will have a limit on how large a block of code you can put into a single variable. data want; infile 'codefile' truncover ; input codeline $32767.; run; ... View more

Have a read of Data Types for Oracle If you want full control what data types you get in the Oracle table - i.e. not just TIMESTAMP but TIMESTAMP(6) then use explicit SQL passthrough and create the Oracle table explicitly before loading into it.   According to the Oracle documentation the default for TIMESTAMP is TIMESTAMP(6) so you should be fine even if leaving the job to SAS. The SAS docu states for automatic conversion "any date, time,....format...": In my experience with no more current releases of SAS things didn't work as expected with formats other than Date and DateTime. But may-be that's fixed now. ... View more

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

Here is :   proc sql; create table X as ( select * from Y a inner join Z b on input(b.emp_id,11.) = a.emp_id where a.sale_dt >= b.sale_dt and a.sale_dt < b.sale_last_dt ); quit;       Make a data step before SQL to convert this variable by INPUT(). data Y1;  set Y; new_emp_id=input(emp_id,11.); run; proc sql; ..................     ... View more

Just use the correct delimiter in the infile statement, and proper informats for the date and datetime values. Since the source of the data was prudent in using ISO 8601 formats, use the proper informats: data want; infile datalines dlm='|' dsd truncover; input var1 var2 var3 $ var4 var5 var6 $ var7 :e8601dz29. var8 :e8601da10. ; format var7 e8601dz29.3 var8 e8601da10. ; datalines; 1234567890|202020| |12345.000000000000|12345.000000000000| |2020-01-01T05:29:50.123-05:00|2022-01-31 ; proc print data=want noobs; run; Result: var1 var2 var3 var4 var5 var6 var7 var8 1234567890 202020 12345 12345 2020-01-01T10:29:50.123+00:00 2022-01-31 ... View more

Hi:   Many mail systems only let you send plain text or HTML in the email body. So for a file like PDF or XLS or XLSX, you do not have any choice other than to send the PDF, or Excel file, as an attachment. There have been many previous postings about how to do this. As a starter, look on page 7 of this paper: http://support.sas.com/resources/papers/proceedings11/300-2011.pdf -- the code snippet shows an RTF file being created with ODS prior to the email step, but you would just change the RTF step or either ODS PDF or ODS EXCEL.   Cynthia ... View more

Since this is a SAS community, the focus is on solving problems using...SAS. If you wish to learn how to to frequency counts etc in Scala, I suggest to turn to a Scala forum instead. ... View more

This link might answer your question. http://support.sas.com/documentation/cdl/en/seclibag/66930/HTML/default/viewer.htm#n1oou0y6xj5eipn1fcacdf3x653y.htm    Also check this out: https://communities.sas.com/t5/Administration-and-Deployment/Metadata-bound-libraries-and-base-table-security/td-p/360717 ... View more

Well the message doesn't really tell what the actual error is. If this is not a temporary connection issue, I'll put a few cents that you are violating a Teradata constraint. Try Options sastrace = ',,,d' sastraceloc = saslog nostsuffix; which might give some info about what's going on in Teradata. ... View more

1. It seems that you have an inner join with a SAS table. If that's filtering the result, a temporary table is needed in TD. 2. Why do want to fetch 250'' rows to SAS? What is the next step? Try to keep as much data and processing in TD as possible. ... View more