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;
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.
@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.
Thanks. I already did that before I asked the question. The problem I have is getting the output to my worksheet
@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.
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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: