SAS MAINFRAME

[Paul2018]

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.

[Kurt_Bremser]

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;       

[Astounding]

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.

[Paul2018]

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.

[Kurt_Bremser]

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.