BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ShagilImam
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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

15 REPLIES 15
LinusH
Tourmaline | Level 20
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
ShagilImam
Obsidian | Level 7

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.

Astounding
PROC Star

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

ShagilImam
Obsidian | Level 7
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.
Cynthia_sas
SAS Super FREQ

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

ShagilImam
Obsidian | Level 7
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 🙂
ShagilImam
Obsidian | Level 7
Anyway, I did learn a lot while looking for a solution myself, so i guess, it was actually time usitlised 🙂
earlex
Fluorite | Level 6

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

earlex
Fluorite | Level 6

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.

ShagilImam
Obsidian | Level 7

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.

earlex
Fluorite | Level 6

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

ShagilImam
Obsidian | Level 7
%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.
earlex
Fluorite | Level 6

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.

ShagilImam
Obsidian | Level 7
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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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