PROC SQL TO DATASET

[rbunster]

Hello,

I have done a simple Mainframe SAS programm which reads an input file, runs a proc sql procedure and I want my results to be written to a dataset, but I don't know how to put my proc sql results to a dataset.

Here is my SAS code:

//GO       EXEC SAS,WORK='1000,500'                      
//SASLIST  DD SYSOUT=*                                   
//ENTRE1 DD DISP=SHR,DSN=PE5A.MFFFF001.S231020G(0)       
//SORTIE DD DSN=UE5A.MFFFF001.COUNT.IV,                 
//          UNIT=SYSALLDA,DISP=(,CATLG,DELETE),         
//          DCB=(DSORG=PS,RECFM=FB,LRECL=100,BLKSIZE=0),
//          SPACE=(CYL,(1,10),RLSE)                     
//SSAVE DD *                                             
//SYSIN DD *                                             
DATA IV;                                                 
   INFILE ENTRE1;                                        
   INPUT @056 SDU                  $CHAR2.               
         @085 SYS_EMET             $CHAR2.               
         @149 ANNE_RECEP           4.                    
         @153 MOIS_RECEP           2.                    
         @279 IND_ASSOC            $CHAR1.;              
   RUN;                                                  
                                                         
PROC SQL;                                                
   CREATE TABLE IV_PREP  AS                              
   SELECT                                                
         SDU,                                            
         SYS_EMET,                                       
         CASE                                            
            WHEN MOIS_RECEP <= 3                         
               THEN ANNE_RECEP - 1                       
            ELSE                                         
               ANNE_RECEP                                
         END AS EXERCICE,                                
         IND_ASSOC                                       
   FROM IV                                               
   ;                                                     
QUIT;                                                    

 PROC SQL;                         
    SELECT                         
          EXERCICE,                
          SDU,                     
          COUNT(SDU) AS QTT_SDU_EXE
    FROM  IV_PREP                  
    GROUP BY EXERCICE , SDU        
    ;                              
 QUIT;                             

/*

//

Thank you

[Reeza]

Interesting...you do it in the preceeding step. Add a create table statement.

PROC SQL;                                                
   CREATE TABLE IV_PREP  AS                              
   SELECT                                                
         SDU,                                            
         SYS_EMET,                                       
         CASE                                            
            WHEN MOIS_RECEP <= 3                         
               THEN ANNE_RECEP - 1                       
            ELSE                                         
               ANNE_RECEP                                
         END AS EXERCICE,                                
         IND_ASSOC                                       
   FROM IV                                               
   ;                                                     
QUIT;  

[rbunster]

But how can I send the table IV_PREP to the dataset
//SORTIE DD DSN=UE5A.MFFFF001.COUNT.IV

like with a put or data _null_ ?

[Reeza]

What type of 'dataset' is this? Please explain in detail. We only know what you tell us. 

[Astounding]

Assuming that SORTIE defines a flat file, you would add a step like this:

data _null_;

file sortie noprint;

set IV_PREP;

put ... whatever it is you want to write, keeping in mind that the record length is only 100 characters;

run;

Under MVS, "noprint" is safe but is probably not really needed.  It is assumed from the characteristics that appear on the DD statement.

[rbunster]

You got it, sortie defines e flat file that is supposed to receive the end result of my las proc SQL.
Do I really have to write something in the put statement ? since I want my whole table on the file, can't I just say like put the whole table ?
Or I have to write all the fields on my select statement ?
Thank you

[Astounding]

You have to spell it out ... all of it.

The source data set is a SAS data set, with a totally different structure internally.

To save on that work, some programmers will do absolutely everything in one step:

data _null_;

infile ENTRE1;

file SORTIE;

input some variables ...

perform some calculations ...

put _infile_ plus a few more variables;

run;

By doing everything in one step, _INFILE_ can be used to be an exact copy of the incoming line of data.  It can easily be written back out, with a few more variables added to the PUT statement.  However, that won't work when your source flat file is (at least) 279 characters long and your destination flat file is defined as only 100 characters long.

[rbunster]

Thank you but it didn't work

see the error message below:

25                                                               
26         PROC SQL;                                             
27            CREATE TABLE IV_CHART1 AS                          
28            SELECT                                             
29                  EXERCICE,                                    
30                  IND_ASSOC,                                   
31                  COUNT(IND_ASSOC)   AS QTT_IND_ASSOC          
32            FROM  IV_PREP                                      
33            GROUP BY EXERCICE, IND_ASSOC                       
34            ;                                                  
NOTE: Table WORK.IV_CHART1 created, with 31 rows and 3 columns.  
35         QUIT;                                                 
NOTE: The PROCEDURE SQL used 5.60 CPU seconds and 23833K.        
NOTE: The address space has used a maximum of 920K below the line 
36                                                               
37         DATA _NULL_;                                          
38         INFILE IV_CHART1;                                     
39         FILE SORTIE;                                          
40         PUT _INFILE_;                                         
41         RUN;  

 ERROR: Invalid file, IV_CHART1.                                    
NOTE: The file SORTIE is:                                          
      Dsname=UE5A.MFFFF001.COUNT.IV,                               
      Unit=3390,Volume=DEV050,Disp=NEW,Blksize=27900,              
      Lrecl=100,Recfm=FB,Creation=2016/09/08                       
                                                                   
NOTE: 0 records were written to the file SORTIE.                   
NOTE: The SAS System stopped processing this step because of errors.
NOTE: The DATA statement used 0.01 CPU seconds and 21913K.          

By everything in one step did you mean my proc sql steps too ?

[Reeza · Accepted solution]

Not sure with the mainframe aspect, but have you looked at proc export?

In your code above you should reference your input dataset with a SET statement not a FILE statement. 

Here are some examples of processing and export in one step. 

http://support.sas.com/documentation/cdl/en/basess/58133/HTML/default/viewer.htm#a002051742.htm

Maybe this post will be helpful:

https://communities.sas.com/t5/ODS-and-Base-Reporting/ODS-CSV-2-challenges/m-p/9005#116724

[rbunster]

Thank you, the SET statement did the trick

DATA _NULL_;
SET IV_CHART;
FILE SORTIE;
PUT _ALL_;
RUN;

[Astounding]

You will need to add an INPUT statement.  If you don't want to input any variables, you don't have to:

INPUT;

But the INPUT statement is what updates the value of _INFILE_.