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:
Names of variables should appear as header in delimited format in the CSV file
Observations should appear as data records in delimited format in the CSV file
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.
