DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

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

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,743

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

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,257

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

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: 9

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,085

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

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: 9

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

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,743

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

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: 9

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

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: 9

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

Anyway, I did learn a lot while looking for a solution myself, so i guess, it was actually time usitlised Smiley Happy
☑ This topic is SOLVED.

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

Discussion stats
  • 7 replies
  • 306 views
  • 5 likes
  • 4 in conversation