topic Re: PROC CONTENTS of entire library to seperate excel sheets in SAS Programming

PROC CONTENTS of entire library to seperate excel sheets

OLUGBOJI — Fri, 31 May 2019 14:46:12 GMT

The code below only prints out the 'variable' ods object to the excel sheets. My question is how do I add two extra ODS objects
called 'attributes' and 'Enginehost' to get the full output of my proc contents to my excel sheets.

ODS OUTPUT variables=allvarout;

proc contents data=sashelp._all_ memtype=data;
run;

proc sort data=allvarout;
by member num;
run;

ODS EXCEL FILE="C:\my\outputfile.xlsx"
options(sheet_name="#BYVAL(member)"
embedded_titles='yes');

proc print data=allvarout noobs;
by member;
pageby member;
title "Variables in #BYVAL(member) data set";
run;

ODS EXCEL CLOSE;

Re: PROC CONTENTS of entire library to seperate excel sheets

ballardw — Fri, 31 May 2019 15:09:12 GMT

@OLUGBOJI wrote:

The code below only prints out the 'variable' ods object to the excel sheets. My question is how do I add two extra ODS objects
called 'attributes' and 'Enginehost' to get the full output of my proc contents to my excel sheets.

ODS OUTPUT variables=allvarout;

proc contents data=sashelp._all_ memtype=data;
run;

proc sort data=allvarout;
by member num;
run;

ODS EXCEL FILE="C:\my\outputfile.xlsx"
options(sheet_name="#BYVAL(member)"
embedded_titles='yes');

proc print data=allvarout noobs;
by member;
pageby member;
title "Variables in #BYVAL(member) data set";
run;

ODS EXCEL CLOSE;

Add additional ODS OUTPUT for ATTRIBUTES and ENGINEHOST just as you have for Variables.

You can find the tables to reference for ODS OUTPUT , SELECT or EXCLUDE using the ODS Trace instruction:

ods trace on;
proc contents data=sashelp.class;
run;
ods trace off;

Will generate the following output in the log:

Output Added:
-------------
Name:       Attributes
Label:      Attributes
Template:   Base.Contents.Attributes
Path:       Contents.DataSet.Attributes
-------------

Output Added:
-------------
Name:       EngineHost
Label:      Engine/Host Information
Template:   Base.Contents.EngineHost
Path:       Contents.DataSet.EngineHost
-------------

Output Added:
-------------
Name:       Variables
Label:      Variables
Template:   Base.Contents.Variables
Path:       Contents.DataSet.Variables
-------------

Output Added:
-------------
Name:       Sortedby
Label:      Sortedby
Template:   Base.Contents.Sortedby
Path:       Contents.DataSet.Sortedby
-------------

The NAME shown in each block is the Table to reference for ODS OUTPUT, just as you did for VARIABLES.

Re: PROC CONTENTS of entire library to seperate excel sheets

OLUGBOJI — Fri, 31 May 2019 16:30:45 GMT

Thanks. I already did that before I asked the question. The problem I have is getting the output to my worksheet

Re: PROC CONTENTS of entire library to seperate excel sheets

ballardw — Fri, 31 May 2019 17:09:47 GMT

@OLUGBOJI wrote:

Thanks. I already did that before I asked the question. The problem I have is getting the output to my worksheet

Since you did not show any code related to creating those data sets or reference them in the ODS destination how were we to know that you had actually done that?

Now it may get interesting. Is your intent to have all three tables on one tab in excel for your "byvar'? Or to have the additional tables on different sheets by themselves?

Provide a dummy example of what you want with the output to be. Manual copy and paste into Excel of a few tables will show this. Then attach the Excel to a message in the forum.

I have a feeling that what you want is not difficult but may take a considerably different approach than you expect. But I am not going to attempt any example code until you can show what the desired output should actually look like.

Re: PROC CONTENTS of entire library to seperate excel sheets

OLUGBOJI — Fri, 31 May 2019 17:31:56 GMT

All I want is 3 tables per worksheet just as you would have in proc contents. Currently my code would output all 3 tables to different sheets. I have about 30 datasets in a particular library and just trying to create a data dictionary for each dataset and output to a worksheet. So I will have 1 workbook that contains 30 sheets

Re: PROC CONTENTS of entire library to seperate excel sheets

ballardw — Fri, 31 May 2019 21:19:55 GMT

You might want to consider:

ODS EXCEL FILE="C:\my\outputfile.xlsx"
   options (sheet_interval='PAGE')

ods exclude directory;
ods exclude members;
proc contents data=sashelp._all_ memtype=data;
run;

ODS EXCEL CLOSE;

If you insist on naming and providing titles then you will need to

1) get a list of datasets

2) set the sheet_name for each individual table

3) set the title text for each proc contents call

4) call proc contents

The above can be done with CALL EXECUTE statements with the data set names in a control file or Macro coding.

Why? The use of the #BY directives ONLY work with a single data set at a time with BY group processing for a single Proc. So you could not use simply by groups to get the desired output. Which will not allow you to have data from three different datasets within a single by group.