Hello,
I am executing a sql query in JCL, using sas, which is running fine, but I want the output in a readable format in a dataset, so that i can extract in CSV format. I am using below JCL
//$SAS9 EXEC SAS,SCL='*',
// CONFIG='KLM.XYZ.LIB(SASCV2P)',WORK='500,500',
// OPTIONS='MACRO,NOCENTRE'
//SAVE01 DD DSN=ABC.DEF.GHI,DISP=OLD
//SASLOG DD SYSOUT=*
//SASLIST DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSIN DD *
proc sql;
connect to db2 (SSID = DB2Y);
create table SAVE01.ABC as
select * from connection to db2 (
SELECT
------------
);
%put sqlXRC = &sqlXRC.;
%put sqlXMSG = &sqlXMSG.;
disconnect from db2;
quit;
endsas;
The output is getting generated in ABC.DEF.GHI and i can read it with TSO SAS. However if I check the same dataset in 3.4, the content is not in simple readable text format. How can i store it in readable text format and so that I can send the output as an attachment in CSV format.
Have a DSN for a text file for SAVE01.
In the code, do this;
proc sql;
connect to db2 (SSID = DB2Y);
create table WORK.ABC as
select * from connection to db2 (
SELECT
------------
);
%put sqlXRC = &sqlXRC.;
%put sqlXMSG = &sqlXMSG.;
disconnect from db2;
quit;
data _null_;
set WORK.ABC;
file SAVE01;
put
/* insert the necessary variables and formats here */
;
run;
As you have seen, PROC SQL produces a SAS data set as its output. There is no simple lever you can flip to make it output a text file instead. You need some sort of tool that takes a SAS data set and converts it to a text file.
@Kurt_Bremser has shown you one of the ways this can be done. That's the safer way, but requires a fair amount of programming. You can also take a look a PROC EXPORT. It takes less work, but might force SAS to make some decisions about what the output should look like.
Thank you @Astounding nad @Kurt_Bremser for your valubale reply. Actually the sad part is I am not much into SAS, or I should say I am still a beginner, my core competancies are in Mainframe. My actual requirement is we receive different SQL queries from different team, which we run in SPUFI > save the result in a dataset > exteact it in local machine > format in excel and then send. So to save all these time I was thinking about to automate this in a single JCL if possible, where we will paste the SQL query and submit, after execution it will automatically sent an email to the recipients with the report as an attachment. I have been trying other possibilites as well, like with an IBM program DSNTEP2, which is running fine and I am getting the required output in .txt format as well; however if there are too may fileds in the query the output goes to the next line (coz DSNTEP2 has some fixed record Length), which I really dont want. Then someone said to go with SAS, ODS Listing etc, so I tried to give it a shot. If i understood @KurtBremser correctly, I can define variables but the thing is we receive different kind of SQL queries everytime, but sure i will give it a try.
If Excel is your final target, and you want your code to be as flexible as possible, I recommend to use proc export as @Astounding already suggested:
proc sql;
connect to db2 (SSID = DB2Y);
create table WORK.ABC as
select * from connection to db2 (
SELECT
------------
);
%put sqlXRC = &sqlXRC.;
%put sqlXMSG = &sqlXMSG.;
disconnect from db2;
quit;
proc export
data=WORK.ABC
outfile=SAVE01
dbms=csv
replace
;
run;
You might even be able to use an Excel destination as dbms, but csv is provided in Base SAS without additional licenses.
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.