topic Re: SAS MAINFRAME in ODS and Base Reporting

SAS MAINFRAME

Paul2018 — Tue, 13 Nov 2018 10:57:33 GMT

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.

Re: SAS MAINFRAME

Kurt_Bremser — Tue, 13 Nov 2018 13:27:54 GMT

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;

Re: SAS MAINFRAME

Astounding — Tue, 13 Nov 2018 16:04:21 GMT

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.

Re: SAS MAINFRAME

Paul2018 — Wed, 14 Nov 2018 10:24:37 GMT

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.

Re: SAS MAINFRAME

Kurt_Bremser — Wed, 14 Nov 2018 10:33:07 GMT

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.