Folks,
Perhaps someone could provide some input into the following, please?
At the moment this is my sample code.
/*Sample code for extracting variable information into an excel sheet*/
/*1 - Read in dataset(s)*/
libname x1 "location1";
libname x2 "location2";
libname x2 "location3";
/*2 - Create dataset(s) with variable info*/
proc contents data=x1._all_
out=contentslisting1;
run;
proc contents data=x1._all_
out=contentslisting1;
run;
/*3- Export data to excel*/
PROC EXPORT DATA=contentslisting1 outfile="location\location\location" dbms=xlsx;
sheet="2009";
run;
PROC EXPORT DATA=contentslisting2 outfile="location\location\location" dbms=xlsx;
sheet="2010";
run;
PROC EXPORT DATA=contentslisting3 outfile="location\location\location" dbms=xlsx;
sheet="2011";
run;
In some cases I might have to export data for 20 years so would have to do each step 20 times. Is there a way to quicken this process even more?
If you're on SAS 9.4 use SASHELP.VTABLE or VCOLUMN to pull the variables of interest + ODS Excel to split into a single Excel workbook.
ODS EXCEL is only available in SAS 9.4+
Also, blog post from yesterday 🙂
Hi, unforunately I do not have SAS 9.4. Is it possible to provide a macro soloution?
Instead of macro please look at this:
proc sql; create table alldata as select * from dictionary.columns where libname in ('X1','X2','X3') order by libname, memname ; quit; ods tagsets.excelxp file="path\file.xml" style=minimal options(sheet_interval="Bygroup"); proc print data=alldata noobs label; by libname; run; ods tagsets.excelxp close;
This creates an XML file that Excel can read. If you truly need an XLSX file when done the do a file save as.
The sheet names will have the library name on the tab so a more descriptive name for the libraries than X1 and X2 would be a good idea.
Note that tagsets.excelxp will report on its own help documentation with this code:
ods tagsets.excelxp options(doc='Quick');
or
ods tagsets.excelxp options(doc='Help');
for the longer version.
Thanks for this, the code is extremley useful. I'm just wondering however, is it limited to a set number of libnames? For instance I'm actually calling from 27 different libraries but it only process up to 9 and stops then?
I don't think there should be any limits in normal practice. If you have enough data sets and variables in them that the total number of rows exceeds what a single sheet, or the number of sheets, Excel can display then you would have had that problem with your original approach.
If you have many libraries to select from it may be simpler in the code to use
Where LIBNAME not in ('SASHELP', 'SASUSER', 'WORK') (and any other libraries to exclude that may have currently defined that you do not want included)
Note that the value of LIBNAME in the dataset created will be all capital letters so you need to use all caps in your syntax.
ODS Tagsets.ExcelXP is another option.
A macro is another option for the export stage.
CALL EXECUTE with proc export is a better idea.
Here's an example but instead of creating a dataset change the code to export.
Using meta data to create mutiple exce sheets in one workbook
inspired by
https://goo.gl/3ekrmk
https://communities.sas.com/t5/SAS-Enterprise-Guide/Export-Variable-names-from-numerous-different-library-locations/m-p/344074
HAVE Meta data and three datasets (Inputs and outputs)
======================================================
Up to 40 obs WORK.META total obs=3
Obs PTHINP SHEET TABLE
1 d:/sd1/ 2009 x1
2 d:/sd2/ 2010 x2
3 d:/sd3/ 2011 x3
Three SAS datasets
d:/sd1/X1.sas7bdat
d:/sd2/X2.sas.bdat
d:/sd3/X3.sas7bdat
X1 Up to 40 obs from sd1.x1 total obs=3
Obs NAME SEX HEIGHT WEIGHT
1 Alfred M 69.0 112.5
2 Alice F 56.5 84.0
3 Barbara F 65.3 98.0
X2 Up to 40 obs from sd2.x2 total obs=3
Obs NAME SEX AGE HEIGHT
1 Alfred M 14 69.0
2 Alice F 13 56.5
3 Barbara F 13 65.3
X3 Up to 40 obs from sd3.x3 total obs=3
Obs NAME SEX AGE WEIGHT
1 Alfred M 14 112.5
2 Alice F 13 84.0
3 Barbara F 13 98.0
WANT (three worksheets 2009, 2010, 2011 in workbook d:/xls/sheets.xlsx)
=======================================================================
Workbook d:/xls/sheets.xlsx
2009 ( Variable names from dataset d/sd1/X1.sas7bdat)
+------------+
| A |
-------------+
1 | VARIABLE |
+------------+
2 | NAME |
3 | SEX |
4 | HEIGHT |
5 | WEIGHT |
+------------+
[2009]
2010 ( Variable names from dataset d/sd1/X2.sas7bdat)
+------------+
| A |
-------------+
1 | VARIABLE |
+------------+
2 | NAME |
3 | SEX |
4 | AGE |
5 | HEIGHT |
+------------+
[2010]
2011 ( Variable names from dataset d/sd1/X3.sas7bdat)
+------------+
| A |
-------------+
1 | VARIABLE |
+------------+
2 | NAME |
3 | SEX |
4 | AGE |
5 | WEIGHT |
+------------+
[2011]
WORKING CODE
===========
1 DOSUBL (load meta data)
2 Datastep code to operate on meta data
3 DOSUBL (create excel sheets
* _ _ _
_ __ ___ __ _| | _____ __| | __ _| |_ __ _
| '_ ` _ \ / _` | |/ / _ \_____ / _` |/ _` | __/ _` |
| | | | | | (_| | < __/_____| (_| | (_| | || (_| |
|_| |_| |_|\__,_|_|\_\___| \__,_|\__,_|\__\__,_|
;
libname sd1 "d:/sd1";
libname sd2 "d:/sd2";
libname sd3 "d:/sd3";
data sd1.x1(drop=age)
sd2.x2(drop=weight)
sd3.x3(drop=height);
set sashelp.class(obs=3);
run;quit;
data meta;
informat pthinp sheet $7. table $2.;
input pthinp sheet table;
cards4;
d:/sd1/ 2009 x1
d:/sd2/ 2010 x2
d:/sd3/ 2011 x3
;;;;
run;quit;
* _ _ _
___ ___ | |_ _| |_(_) ___ _ __
/ __|/ _ \| | | | | __| |/ _ \| '_ \
\__ \ (_) | | |_| | |_| | (_) | | | |
|___/\___/|_|\__,_|\__|_|\___/|_| |_|
;
%utlfkil(d:/xls/sheets.xlsx);
libname xel "d:/xls/sheets.xlsx";
data _null_;
if _n_=0 then do;
%let rc=%sysfunc(dosubl('
proc sql;
select
quote(trim(pthinp))
,quote(trim(sheet))
,quote(trim(table))
into
:inp separated by ","
,:she separated by ","
,:tbl separated by ","
from
meta
;quit;
'));
end;
array inps[&sqlobs] $7 (&inp);
array shes[&sqlobs] $4 (&she);
array tbls[&sqlobs] $32 (&tbl);
do i=1 to &sqlobs;
call symputx('inpx',inps[i]);
call symputx('shex',shes[i]);
call symputx('tblx',tbls[i]);
rc=dosubl('
libname inp "&inpx.";
proc transpose data=inp.&tblx.(obs=1)
out=xel.f&shex.(rename=_name_=variable);
var _all_;
run;quit;
');
end;
run;quit;
libname xel clear;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: