DATA Step, Macro, Functions and more

Exporting a Dataset on Z/OS (Without using PROC EXPORT)

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

Exporting a Dataset on Z/OS (Without using PROC EXPORT)

[ Edited ]

Hi All, 

 

I work on SAS on Z/OS platform; we use Interactive SAS for online viewing/editing of SAS datasets. However, it is still not very user friendly as we have to use keyboard commands for every little function. 

Many a times, we need to analyse some SAS datasets to check whether or not our programs have given the expected output. Using Interactive SAS for this purpose (epecially when there are too many variables/observations) is a bit cumbersome and inefficient. Sadly, PROC EXPORT does not work on Z/OS platform, so there is no easy way to export the file in CSV format so that we can download it to windows and then analyse using MS Excel. 

 

(It would be great if we could do this!)

With this in mind, I had to come up with a solution to be able to export any SAS Dataset including variable names as header record in CSV file. Here I am sharing my solution to the problem: 

 

Problem Statement

PUT statement can be used to write an observation to a CSV flat file, however, for the generated CSV file to be usable for analysis using MS Excel, we must also know the names of variables to which each of the values in the CSV file belong. Also, the method should work with any SAS data set simply by specifying the names of the SAS dataset.

The problem statements can be written down as following points:

  1. Names of variables should appear as header in delimited format in the CSV file

  2. Observations should appear as data records in delimited format in the CSV file

  3. Names of variables should correspond to its values by position in the CSV file, that is, if variable VAR1 is at position 9 (after 8 delimiters) then all its values should also be at position 9.

 

Solution

We will tackle each of the problem statements one by one. For the sake of simplifying things for later, we will assign the dataset name to a macro variable, and also write LIBNAME statement for SAS Library and FILENAME name statement for the CSV flat file.

 

LIBNAME Libref <SAS Library that holds datset which is to be exported> DISP=SHR;

FILENAME Filref <Output flat file to which the dataset will be exported> DISP=OLD;

On Z/OS platform, the above LIBNAME and FILENAME statements can be achieved using DD Statements in JCL.

 

%Let DatasetName = <name of the dataset to be exported in the form Libref.datasetname>

 

Names of variables should appear as header in delimited format in the CSV file.

We can use the PROC CONTENTS procedure with NOPRINT option and keep only the 'Name' variable to get the list of all variable names in the dataset.

 

Proc Contents Data=&DatasetName Out=Work.VarList (Keep=Name) Noprint;

Run;

 

The code above will create a work dataset VarList with only one variable - 'Name'. This variable will give the list of all variables in the dataset given by &DatasetName.

 

However, as we need to write the names of variables as first record in the CSV file, hence, we will need the names of variables as an observation in a dataset. PROC TRANSPOSE can be used to achieve this requirement.

 

Proc Transpose Data= Work.VarList Out=Work.DatasetVars (Drop= _NAME_ _LABEL_);

Var_ALL_;

Run;

 

The code above will create a dataset Work.DatasetVars which will have a single observation which has the names of all variables in the dataset given by &DatasetName.

 

Next step is to write the observation in Work.DatasetVars as the first record in the CSV file.

In general, PUT statement is used to write a record to a flat file. Syntax followed is:

PUT Variable1 Variable2 ....VariableN;

If we want to write values of all variables to the flat file, then we can use

PUT (_ALL_)(+0);

 

Code will be as follows:

Data _NULL_;

File CSVFILE DELIMITER=';' DSD DROPOVER;

Set Work.DatasetVars;

Put (_ALL_)(+0);

Run;

 

Explanation:

(_ALL_) is a variable list that specifies all variables that are currently defined in the current DATA step.

(_ALL_) expects to be followed by a delimiter. (+0) signifies that no delimiter should be added by the PUT statement.

There is only one observation in the dataset Work.DatasetVars and it is the names of all variables in different columns. Thus the names of variables will be written to the output flat file in a semicolon delimited format.

 

Observations should appear as data records

Similar to the above step Put (_ALL_)(+0) can be used to write the observations to the output file in semicolon delimited format, however, an important requirement is that the values of variables should appear at the same position (in terms of delimiters) as the names of their corresponding variables.

 

Names of variables should correspond to its values by position in the CSV file

We can use the RETAIN statement to sequence the variables as required. For example, if we write:

RETAIN Var1;

RETAIN Var2;

RETAIN Var3;

.

.

.

RETAIN VarN;

 

The above will mean that the variables Var1, Var2, Var3, ....VarN will appear in the sequence in which RETAIN statements for each of the variables appears in the data step.

Therefore, in order to align the data record variables with the header record variable names, all we need to do is issue a RETAIN statement for each of the values in the variable 'Name' of dataset Work.VarList.

We will need to utilize SAS macro to fetch records from the dataset Work.VarList one by one, and will need to generate a RETAIN statement for each of the variable names.

This can be done as follows:

 

%macro RetainVars;

    %let dsid=%sysfunc(open(Work.VarList));

    %do %until (&rc ne 0);

        %let rc = %sysfunc(fetch(&dsid));

        %if &rc ne 0 and &rc ne -1 %then %put %sysfunc(sysmsg());

        %else %do;

            %let varname = %sysfunc(getvarc(&dsid,%sysfunc(varnum(&dsid,Name))));

            RETAIN &varname;

        %end;

    %end;

%mend Retainvars;

 

Now that we have the macro to write the retain statements, we can write the data step to export the observations to the CSV file.

 

Data _NULL_ ;

File CSVFILE MOD DELIMITER=';' DROPOVER;

%RetainVars;

Set &DatasetName;

Put (_ALL_)(+0);

Run;

 

If any explanation is required, or you have any comment, please let me know. Hope this post will be helpful to someone like me out there.

 

P.S.  - I had to refer to a lot many community posts, papers, and third party websites in order to come up with the solution. Many thanks to all those who are very kind to share their knowledge.


Accepted Solutions
Solution
‎01-22-2017 10:09 AM
SAS Super FREQ
Posts: 8,866

Re: Exporting a Dataset on Z/OS (Without using PROC EXPORT)

Posted in reply to ShagilImam

Hi:

  For completeness, ODS CSV will work on z/OS to make a CSV file without using a DATA step program. In addition, there is an older macro program called %DS2CSV, that should still be available as described here https://support.sas.com/documentation/cdl/en/lebaseutilref/64791/HTML/default/viewer.htm#n0yo3bszlrh... .

 

cynthia

View solution in original post


All Replies
Super User
Posts: 5,429

Re: Exporting a Dataset on Z/OS (Without using PROC EXPORT)

Posted in reply to ShagilImam
Nice share. Kudos for giving!
A reflection though, that this solution sounds like a band aid on a broken leg. If you have the means I would strongly recommend you to look an Integration Technologies souring that wold give you to use light weight GUI clients such as SAS Studio.
Data never sleeps
Occasional Contributor
Posts: 13

Re: Exporting a Dataset on Z/OS (Without using PROC EXPORT)

Thanks. I surely would love to have a GUI client for viewing the data. We do have 'Interactive SAS', but it is a bit cumbersome to use.
Also, when the output dataset is in CSV format in a text file, I can attach it in my test plan document as a proof of expected output.

What I shared above, simply does what PROC EXPORT does on Windows or Unix platform. But unfortunately, PROC EXPORT did not work on the environment that we use (Z/OS),  and hence, I had to come up with a different solution, shared above.

Super User
Posts: 5,505

Re: Exporting a Dataset on Z/OS (Without using PROC EXPORT)

Posted in reply to ShagilImam

On Z/OS, PROC CPORT will create a transport file (and PROC CIMPORT will read transport files).  Are you saying that you haven't found a way to read a transport file on the other platform(s)?

Occasional Contributor
Posts: 13

Re: Exporting a Dataset on Z/OS (Without using PROC EXPORT)

Posted in reply to Astounding
Thanks.
My main requirement was to have the content of the dataset in CSV file, along with the names of variables as header record; similar to what PROC EXPORT does. I have simply shared the solution that I came up with.
Solution
‎01-22-2017 10:09 AM
SAS Super FREQ
Posts: 8,866

Re: Exporting a Dataset on Z/OS (Without using PROC EXPORT)

Posted in reply to ShagilImam

Hi:

  For completeness, ODS CSV will work on z/OS to make a CSV file without using a DATA step program. In addition, there is an older macro program called %DS2CSV, that should still be available as described here https://support.sas.com/documentation/cdl/en/lebaseutilref/64791/HTML/default/viewer.htm#n0yo3bszlrh... .

 

cynthia

Occasional Contributor
Posts: 13

Re: Exporting a Dataset on Z/OS (Without using PROC EXPORT)

Posted in reply to Cynthia_sas
Thanks Cynthia. %DS2CSV is what I really needed. I wish it had appeared in my google searches; would have saved me a lot of time Smiley Happy
Occasional Contributor
Posts: 13

Re: Exporting a Dataset on Z/OS (Without using PROC EXPORT)

Posted in reply to ShagilImam
Anyway, I did learn a lot while looking for a solution myself, so i guess, it was actually time usitlised Smiley Happy
Occasional Contributor
Posts: 5

Re: Exporting a Dataset on Z/OS (Without using PROC EXPORT)

Posted in reply to Cynthia_sas

Hi Cynthia,

 

I tried to run the DS2CSV macro on z/os (in batch mode) and it doesn't like my inbound file reference.  The file to be copied is a SAS library.  I hope the macro will copy a SAS library file to a flat CSV file on the mainframe.  Here is the JCL:

 

//*------------------------------------------------------------------
//*        DUMP OUT SAS LIBRARY XXMT007
//*------------------------------------------------------------------
//LETSGO EXEC SAS
//WORK     DD UNIT=SYSDA,SPACE=(CYL,(1000,1000),,,ROUND)
//*XMT007  DD DSN=SGSM.PXXMT07.SASTAGSU,DISP=SHR
//SYSIN    DD *
FILENAME XXMT007 'SGSM.PXXMT07.SASTAGSU';
FILENAME XXMTCSV 'SGSM.CXXMT07.SASTAGSU.CSV';
%DS2CSV (DATA=XXMT007,RUNMODE=B,CSVFREF=XXMTCSV);
//

 

Here is the syntax error:


1          FILENAME XXMT007 'SGSM.PXXMT07.SASTAGSU';
2          FILENAME XXMTCSV 'SGSM.CXXMT07.SASTAGSU.CSV';
3          %DS2CSV (DATA=XXMT007,RUNMODE=B,CSVFREF=XXMTCSV);
ERROR: File WORK.XXMT007.DATA does not exist.
NOTE: Execution terminating...

 

Any suggestions?

 

Thanks,

Mike

Occasional Contributor
Posts: 5

Re: Exporting a Dataset on Z/OS (Without using PROC EXPORT)

Posted in reply to ShagilImam

Hi Shagillman,

I am trying to run your macro on z/OS. I copied the code and upper-cased it:

 

%MACRO RETAINVARS;
 %LET DSID=%SYSFUNC(OPEN(WORK.VARLIST));
 %DO UNTIL (&RC NE 0);
  %LET RC=%SYSFUNC(FETCH(&DSID));
  %IF &RC NE 0 AND &RC NE -1 %THEN %PUT %SYSFUNC(SYSMSG());
  %ELSE %DO;
   %LET VARNAME=%SYSFUNC(GETVARC(&DSID,%SYSFUNC(VARNUM(&DSID,NAME))));
   RETAIN &VARNAME;
  %END;
 %END;
%MEND RETAINVARS;

 

I am getting syntax (?) errors as follows:

 

ERROR: An unexpected semicolon occurred in the %DO statement.
ERROR: A dummy macro will be compiled.
ERROR: There is no matching %IF statement for the %ELSE.

ERROR: SAS ended due to errors.
       You specified: OPTIONS ERRORABEND;.
ERROR: Errors printed on page 1.

 

I am well versed in z/OS, but not SAS.  So I am looking for some help with the above errors.

 

BTW, I am trying to copy a SAS library to a CSV file on the mainframe.  I hope your SAS program will do this.  Thanks.

Occasional Contributor
Posts: 13

Re: Exporting a Dataset on Z/OS (Without using PROC EXPORT)

Hello earlex,

 

There should be a %UNTIL after the %DO statement.

%DO %UNTIL (&RC NE 0);

 

I see, I have missed it in my post. Apologies.

 

However, I will suggest that you use the standard DS2CSV macro.

Occasional Contributor
Posts: 5

Re: Exporting a Dataset on Z/OS (Without using PROC EXPORT)

Posted in reply to ShagilImam

Hi Shagillman,

Thanks for the correction to your macro.  I did try the DS2CSV and I also have a syntax or format error. You can see this on the post to Cynthia_SAS just before I posted the question to you.  Do you have a minute to look at that post?

Thanks,

earlex

Occasional Contributor
Posts: 13

Re: Exporting a Dataset on Z/OS (Without using PROC EXPORT)

%DS2CSV (DATA=XXMT007,RUNMODE=B,CSVFREF=XXMTCSV);

Here, try giving the fileref.datasetname with the parameter DATA=

You have only mentioned what you think is the fileref, but, the macro is expecting name of the dataset.
Occasional Contributor
Posts: 5

Re: Exporting a Dataset on Z/OS (Without using PROC EXPORT)

Posted in reply to ShagilImam

Hi Shagillmam,

 

Here is my last test:

 

//LETSGO EXEC SAS
//WORK     DD UNIT=SYSDA,SPACE=(CYL,(1000,1000),,,ROUND)
//*XMT007  DD DSN=SGSM.PXXMT07.SASTAGSU,DISP=SHR
//*YSIN    DD DSN=SGSM.IBM.SOURCE(DXXMT007),DISP=SHR
//SYSIN    DD *
*ILENAME XXMT007 'SGSM.PXXMT07.SASTAGSU';
FILENAME XXMTCSV 'SGSM.CXXMT07.SASTAGSU.CSV';
%DS2CSV (DATA=SGSM.PXXMT07.SASTAGSU,RUNMODE=B,CSVFREF=XXMTCSV);
//

 

I get this error...so it looks like it needs the reference.

 

ERROR: SGSM.PXXMT07.SASTAGSU is an invalid name for the SAS data set to be formatted.

 

Have you tried this DS2CSV macro on your z/os environment?  I'd like to see the whole job for a successful run.  Thanks.

Occasional Contributor
Posts: 13

Re: Exporting a Dataset on Z/OS (Without using PROC EXPORT)

I have not used DS2CSV, I just use the macro that I had created (my post). You can use it if it works for you.
However, from the macro call you are using, it is clear that you are not providing the correct name of the SAS dataset which you want in CSV file.
I assume that 'SGSM.PXXMT07.SASTAGSU' is the Z/OS sas library. If so, SAS dataset is contained in this library. (There may be more than one SAS dataset within this library.)
Now, as you have assigned a fileref 'XXMT007' to this library, you can refer to it by this fileref.
You need to know the name of the dataset which you want in the CSV file, let's say it is Dataset1.
Then, the macro call should look like:
%DS2CSV (DATA=XXMT007.Dataset1,RUNMODE=B,CSVFREF=XXMTCSV);

P.S. what i have mentioned above is only about providing correct dataset name to the macro. I can not say that the remainig parameters have been correctly set.
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 15 replies
  • 494 views
  • 10 likes
  • 5 in conversation