topic Re: PROC SQL TO DATASET in SAS Procedures

PROC SQL TO DATASET

rbunster — Wed, 07 Sep 2016 20:36:34 GMT

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

Re: PROC SQL TO DATASET

Reeza — Wed, 07 Sep 2016 20:40:43 GMT

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;

Re: PROC SQL TO DATASET

rbunster — Wed, 07 Sep 2016 20:46:55 GMT

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_ ?

Re: PROC SQL TO DATASET

Reeza — Wed, 07 Sep 2016 21:06:22 GMT

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

Re: PROC SQL TO DATASET

Astounding — Wed, 07 Sep 2016 21:10:46 GMT

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.

Re: PROC SQL TO DATASET

rbunster — Wed, 07 Sep 2016 21:21:31 GMT

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

Re: PROC SQL TO DATASET

Astounding — Wed, 07 Sep 2016 21:27:34 GMT

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.

Re: PROC SQL TO DATASET

rbunster — Thu, 08 Sep 2016 13:27:53 GMT

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 ?

Re: PROC SQL TO DATASET

Reeza — Thu, 08 Sep 2016 12:58:41 GMT

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

Re: PROC SQL TO DATASET

Astounding — Thu, 08 Sep 2016 13:11:29 GMT

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

Re: PROC SQL TO DATASET

rbunster — Thu, 08 Sep 2016 17:21:50 GMT

Thank you, the SET statement did the trick

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