a poor/rich sas® user's proc export - toronto area sas society
TRANSCRIPT
Next Presentation:
Presenter: Arthur Tabachneck
A Poor/Rich SAS® User's Proc Export
Art holds a PhD from Michigan State University, has been a SAS user since 1974, is president of the Toronto Area SAS Society and has received such recognitions as the SAS Customer Value Award (2009), SAS-L Hall of Fame (2011), SAS Circle of Excellence (2012) and, in 2013, was recognized as being the first SAS Discussion Forum participant to be awarded more than 10,000 points
Randy Herbison Rockville, MD
Arthur Tabachneck Thornhill, ON Canada
Tom Abernathy New York, NY
Matt Kastin Penn Valley, PA
How many of you are able to export SAS
datasets to Excel using PROC EXPORT?
Question
Would you like to automate the process to be a
point-and-click menu option?
If your answer was "YES"
Would you like to be able to export SAS datasets
to Excel, without requiring PROC EXPORT, and
automate the process to be a point-and-click
menu option?
Regardless of whether your answer was "YES or "NO"
and, of course, in both cases:
in ways that appear to accomplish
the tasks almost automagically
and suppose that
you may only have base SAS
but, regardless,
you may or may not be a SAS guru
you’d really like to impress the people you
work with and/or make SAS easier to use
what the solutions look like
Right click on a dataset name
in the SAS Explorer window
To run PROC EXPORT – select the desired Action (i.e., press hot key (E) or point and left-click)
to run PROC EXPORT – part 2 – Export to Excel
There is no part 2! The workbook would already have been created!
to run Export to Clipboard
Right click on a dataset name
in the SAS Explorer window
To run Export to Clipboard – select the desired Action (i.e., press hot key (X) or point and left-click)
the solution – part 2 – for Export to Clipboard
Open Excel 1
the solution – part 2 – for Export to Clipboard
Open Excel – Press Ctrl-V (Paste) 1 2
the method also works with a formatted template
Open Excel Template – Press Ctrl-V (Paste)
1 2
such capabilities are only moments away with
the methods described in this presentation
how to do either/both
Left-click anywhere in the SAS Explorer Window
how to do either/both
Left-click on Tools, move your mouse to Options→Explorer
how to do either/both
Left-click on Members
how to do either/both
Then double left-click on Table
how to do either/both
left-click on Add and an Add Action screen will appear
how to do either/both
Type the text you want to add to the menu
&Export to Excel
Action
&Export to Excel
It will appear on the menu as: Export to Excel
how to create the Export to Excel option
Left click in the Action Command box
how to create the Export to Excel option
&Export to Excel
Then type the action command you want SAS to apply
Action Command gsubmit '%%let filepath=%%sysfunc(pathname(%8b)); %%let filenm=%s; PROC EXPORT DATA=%8b.%s OUTFILE= "&filepath.\&filenm..xlsx" DBMS=XLSX REPLACE;RUN;';
gsubmit '%%let filepath=%%sysfunc(p
how to create the Export to Excel option
&Export to Excel
The action command
gsubmit ' %%let filepath=%%sysfunc(pathname(%8b)); %%let filenm=%s; PROC EXPORT DATA=%8b.%s OUTFILE= "&filepath.\&filenm..xlsx" DBMS=XLSX REPLACE; RUN; ';
how to create the Export to Excel option
The action command
gsubmit ' %%let filepath=%%sysfunc(pathname(%8b)); %%let filenm=%s; PROC EXPORT DATA=%8b.%s OUTFILE= "&filepath.\&filenm..xlsx" DBMS=XLSX REPLACE; RUN; ';
how to create the Export to Excel option
create macro variable containing
full path of the dataset's libname
%% %% %8b
The action command
gsubmit ' %%let filepath=%%sysfunc(pathname(%8b)); %%let filenm=%s; PROC EXPORT DATA=%8b.%s OUTFILE= "&filepath.\&filenm..xlsx" DBMS=XLSX REPLACE; RUN; ';
how to create the Export to Excel option
create macro variable containing
dataset's filename
%s
The action command
gsubmit ' %%let filepath=%%sysfunc(pathname(%8b)); %%let filenm=%s; PROC EXPORT DATA=%8b.%s OUTFILE= "&filepath.\&filenm..xlsx" DBMS=XLSX REPLACE; RUN; ';
how to create the Export to Excel option
libname
%8b
filename
%s
Click on OK to exit the Add Action screen
how to create the Export to Excel option
gsubmit '%%let filepath=%%sysfunc(p
&Export to Excel
Click on OK to exit the Table Options screen
how to create the Export to Excel option
then, whenever you right-click on a file and select the option (i.e., press hot key (E) or point and left-click)
the Excel workbook will be automagically created
Type the text you want to add to the menu
E&xport to Clipboard
Action
E&xport to Clipboard
It will appear on the menu as: Export to Clipboard
how to create the Export to Clipboard option
Left click in the Action Command box
how to create the Export to Clipboard option
E&xport to Clipboard
Then type the action command you want SAS to apply
Action Command gsub 'proc transpose data=%8b.%s(obs=0);var _all_;proc sql noprint;select _name_ into :_ separated by " " from &syslast;drop table &syslast;filename c clipbrd;data _null_;file c dsd dlm=" "; set %8b.%s;if _n_=1 then put "&_";put(_all_)(:);run;filename c;';
gsub ‘proc transpose data=%8b.%s(ob
how to create the Export to Clipboard option
E&xport to Clipboard
Art Carpenter would be proud (?) of us
However, it wasn't for job security The action command will only accept 255 characters
(the characters shown in red weren't needed)
gsubmit 'proc transpose data=%8b.%s (obs=0); var _all_; run; proc sql noprint; select _name_ into :_names separated by " " from &syslast; drop table &syslast; quit; filename clippy clipbrd; data _null_; file clippy dsd dlm=" "; set %8b.%s; if _n_=1 then put "&_names"; put(_all_)(:); run; filename clippy clear;';
However, it wasn't for job security The action command will only accept 255 characters
(the characters shown in red weren't needed)
gsubmit 'proc transpose data=%8b.%s (obs=0); var _all_; run; proc sql noprint; select _name_ into :_names separated by " " from &syslast; drop table &syslast; quit; filename clippy clipbrd; data _null_; file clippy dsd dlm=" "; set %8b.%s; if _n_=1 then put "&_names"; put(_all_)(:); run; filename clippy clear;';
Explanation
1. gsub is an acceptable abbreviation for gsubmit
2. gsub(mit) will submit all of the text that appears between the two single quotation marks
However, it wasn't for job security The action command will only accept 255 characters
(the characters shown in red weren't needed)
gsubmit 'proc transpose data=%8b.%s (obs=0); var _all_; run; proc sql noprint; select _name_ into :_names separated by " " from &syslast; drop table &syslast; quit; filename clippy clipbrd; data _null_; file clippy dsd dlm=" "; set %8b.%s; if _n_=1 then put "&_names"; put(_all_)(:); run; filename clippy clear;';
Explanation
1. Proc Transpose is used to create a file containing _all_ variable names
2. Data file is declared by specifying %8b and %s
3. Obs is set to 0 so no data will be read
4. No out option is used so SAS will assign a proc generated filename
However, it wasn't for job security The action command will only accept 255 characters
(the characters shown in red weren't needed)
gsubmit 'proc transpose data=%8b.%s (obs=0); var _all_; run; proc sql noprint; select _name_ into :_names separated by " " from &syslast; drop table &syslast; quit; filename clippy clipbrd; data _null_; file clippy dsd dlm=" "; set %8b.%s; if _n_=1 then put "&_names"; put(_all_)(:); run; filename clippy clear;';
Explanation
1. Proc SQL is used to create a tab delimited macro variable, called _, containing the variable names
2. The tab character was placed in the code by typing Alt 09 thus only using 1 character
3. By using &syslast, the file created by Proc Transpose is used as the from file
4. &syslast is deleted using a drop table statement
5. SQL will automatically quit at next boundary
However, it wasn't for job security The action command will only accept 255 characters
(the characters shown in red weren't needed)
gsubmit 'proc transpose data=%8b.%s (obs=0); var _all_; run; proc sql noprint; select _name_ into :_names separated by " " from &syslast; drop table &syslast; quit; filename clippy clipbrd; data _null_; file clippy dsd dlm=" "; set %8b.%s; if _n_=1 then put "&_names"; put(_all_)(:); run; filename clippy clear;';
Explanation
1. filename c is declared using clipbrd access method
2. c is declared as the file to write to using the dsd option and the tab character was declared as the dlm by typing Alt 09 thus only using 1 character
3. The data file is set using %8b (the libname) and %s (the filename)
4. when _n_ equals 1 the tab-delimited variable names (in macro variable &_) are written to c
5. the data is written to c using a put statement
Click on OK to exit the Add Action screen
gsub ‘proc transpose data=%8b.%s(ob
how to create the Export to Clipboard option
E&xport to Clipboard
Click on OK to exit the Table Options screen
how to create the Export to Clipboard option
then, whenever you right-click on a file and select the option (i.e., press hot key (X) or point and left-click)
You can paste the file (Ctrl-V) into a workbook
notes
Our paper describes two methods for getting around the 255 character limitation, one by using a macro, and another by creating a function with Proc FCMP. You could also %include a file or submit SAS/AF code
SAS Explorer comes with a built-in action called Copy Contents to Clipboard, but it creates an HTML file, contains some possibly undesired headers, shading and borders and runs 138.67 times slower than the methods we've proposed
We selected the first method because it doesn't require an external file or the declaration of an autocall library
Our FCMP code is our only proposed CopytoClipboard method that will copy more than 256 characters
Create the following sas program and store it in a directory that exists in your
SASAUTOS* path
Getting around BOTH limitations – macro + PROC FCMP
* see: http://analytics.ncsu.edu/sesug/2008/SBC-126.pdf
%macro copy2clipbrd(lib,mem); proc fcmp outlib=work.func.util; function c2cb( lib $ , mem $ ); rc = filename( 'clippy' , ' ' , 'clipbrd' ); if ( rc ne 0 ) then return( 1 ); fid = fopen( 'clippy' , 'o' , 0 , 'v' ); if ( fid = 0 ) then return( 2 ); dsid = open( catx( '.' , lib , mem ) ); if ( dsid = 0 ) then return( 3 ); nvar = attrn( dsid , 'nvar' ); array v[1] /nosymbols; call dynamic_array( v , nvar ); rc = attrn( dsid , 'any' ); rc = fput( fid , varname( dsid , 1 ) ); v[1] = ifn( vartype( dsid , 1 ) = 'C' , 1 , 2 ); do i = 2 to nvar; rc = fput( fid , '09'x ); rc = fput( fid , varname( dsid , i ) ); v[i] = ifn( vartype( dsid , i ) = 'C' , 1 , 2 ); end; rc = fwrite( fid );
Getting around BOTH limitations – macro + PROC FCMP
%macro copy2clipbrd(lib,mem); proc fcmp outlib=work.func.util; function c2cb( lib $ , mem $ ); rc = filename( 'clippy' , ' ' , 'clipbrd' ); if ( rc ne 0 ) then return( 1 ); fid = fopen( 'clippy' , 'o' , 0 , 'v' ); if ( fid = 0 ) then return( 2 ); dsid = open( catx( '.' , lib , mem ) ); if ( dsid = 0 ) then return( 3 ); nvar = attrn( dsid , 'nvar' ); array v[1] /nosymbols; call dynamic_array( v , nvar ); rc = attrn( dsid , 'any' ); rc = fput( fid , varname( dsid , 1 ) ); v[1] = ifn( vartype( dsid , 1 ) = 'C' , 1 , 2 ); do i = 2 to nvar; rc = fput( fid , '09'x ); rc = fput( fid , varname( dsid , i ) ); v[i] = ifn( vartype( dsid , i ) = 'C' , 1 , 2 ); end; rc = fwrite( fid );
Getting around BOTH limitations – macro + PROC FCMP
Create a macro that:
1. has 2 parameters (the libname and memname that will be passed by the gsubmit call)
2. uses PROC FCMP to create a function that will do all of the work
3. runs the function
4. cleans up after itself
%macro copy2clipbrd(lib,mem); proc fcmp outlib=work.func.util; function c2cb( lib $ , mem $ ); rc = filename( 'clippy' , ' ' , 'clipbrd' ); if ( rc ne 0 ) then return( 1 ); fid = fopen( 'clippy' , 'o' , 0 , 'v' ); if ( fid = 0 ) then return( 2 ); dsid = open( catx( '.' , lib , mem ) ); if ( dsid = 0 ) then return( 3 ); nvar = attrn( dsid , 'nvar' ); array v[1] /nosymbols; call dynamic_array( v , nvar ); rc = attrn( dsid , 'any' ); rc = fput( fid , varname( dsid , 1 ) ); v[1] = ifn( vartype( dsid , 1 ) = 'C' , 1 , 2 ); do i = 2 to nvar; rc = fput( fid , '09'x ); rc = fput( fid , varname( dsid , i ) ); v[i] = ifn( vartype( dsid , i ) = 'C' , 1 , 2 ); end; rc = fwrite( fid );
Getting around BOTH limitations – macro + PROC FCMP
Run PROC FCMP to create a function called c2cb that has 2 parameters (the libname and
memname that will be passed by the macro)
%macro copy2clipbrd(lib,mem); proc fcmp outlib=work.func.util; function c2cb( lib $ , mem $ ); rc = filename( 'clippy' , ' ' , 'clipbrd' ); if ( rc ne 0 ) then return( 1 ); fid = fopen( 'clippy' , 'o' , 0 , 'v' ); if ( fid = 0 ) then return( 2 ); dsid = open( catx( '.' , lib , mem ) ); if ( dsid = 0 ) then return( 3 ); nvar = attrn( dsid , 'nvar' ); array v[1] /nosymbols; call dynamic_array( v , nvar ); rc = attrn( dsid , 'any' ); rc = fput( fid , varname( dsid , 1 ) ); v[1] = ifn( vartype( dsid , 1 ) = 'C' , 1 , 2 ); do i = 2 to nvar; rc = fput( fid , '09'x ); rc = fput( fid , varname( dsid , i ) ); v[i] = ifn( vartype( dsid , i ) = 'C' , 1 , 2 ); end; rc = fwrite( fid );
Getting around BOTH limitations – macro + PROC FCMP
Assign fileref/open files and ensure all operations are successful
1. assign and open 'clippy' using the clipbrd access method
2. open the input file
3. return error codes if any operation isn't successful
%macro copy2clipbrd(lib,mem); proc fcmp outlib=work.func.util; function c2cb( lib $ , mem $ ); rc = filename( 'clippy' , ' ' , 'clipbrd' ); if ( rc ne 0 ) then return( 1 ); fid = fopen( 'clippy' , 'o' , 0 , 'v' ); if ( fid = 0 ) then return( 2 ); dsid = open( catx( '.' , lib , mem ) ); if ( dsid = 0 ) then return( 3 ); nvar = attrn( dsid , 'nvar' ); array v[1] /nosymbols; call dynamic_array( v , nvar ); rc = attrn( dsid , 'any' ); rc = fput( fid , varname( dsid , 1 ) ); v[1] = ifn( vartype( dsid , 1 ) = 'C' , 1 , 2 ); do i = 2 to nvar; rc = fput( fid , '09'x ); rc = fput( fid , varname( dsid , i ) ); v[i] = ifn( vartype( dsid , i ) = 'C' , 1 , 2 ); end; rc = fwrite( fid );
Getting around BOTH limitations – macro + PROC FCMP Do the following:
1. get number of variables
2. create dynamic array
3. get and write tab-delimited variable names
4. assign variable types to array
5. write variable names to clipboard
do i = 1 to attrn( dsid , 'nlobs' ); rc = fetchobs( dsid , i ); if ( v[1] = 1 ) then rc = fput( fid , getvarc( dsid , 1 )); else do; fmt = varfmt( dsid , 1 ) ; if missing( fmt ) then fmt = 'best12.'; rc=fput(fid,putc(putn(getvarn(dsid,1),fmt),'$char12.')); end; do j = 2 to nvar; rc = fput( fid , '09'x ); if ( v[j] = 1 ) then rc = fput( fid , getvarc( dsid , j ) ); else do; fmt = varfmt( dsid , j ) ; if missing( fmt ) then fmt = 'best12.'; rc=fput(fid,putc(putn(getvarn(dsid,j ),fmt ),'$char12.') ); end; end; rc = fwrite( fid ); end;
Getting around BOTH limitations – macro + PROC FCMP
Get and write the data for all records
do i = 1 to attrn( dsid , 'nlobs' ); rc = fetchobs( dsid , i ); if ( v[1] = 1 ) then rc = fput( fid , getvarc( dsid , 1 )); else do; fmt = varfmt( dsid , 1 ) ; if missing( fmt ) then fmt = 'best12.'; rc=fput(fid,putc(putn(getvarn(dsid,1),fmt),'$char12.')); end; do j = 2 to nvar; rc = fput( fid , '09'x ); if ( v[j] = 1 ) then rc = fput( fid , getvarc( dsid , j ) ); else do; fmt = varfmt( dsid , j ) ; if missing( fmt ) then fmt = 'best12.'; rc=fput(fid,putc(putn(getvarn(dsid,j ),fmt ),'$char12.') ); end; end; rc = fwrite( fid ); end;
Getting around BOTH limitations – macro + PROC FCMP
Write out the data for the first variable
do i = 1 to attrn( dsid , 'nlobs' ); rc = fetchobs( dsid , i ); if ( v[1] = 1 ) then rc = fput( fid , getvarc( dsid , 1 )); else do; fmt = varfmt( dsid , 1 ) ; if missing( fmt ) then fmt = 'best12.'; rc=fput(fid,putc(putn(getvarn(dsid,1),fmt),'$char12.')); end; do j = 2 to nvar; rc = fput( fid , '09'x ); if ( v[j] = 1 ) then rc = fput( fid , getvarc( dsid , j ) ); else do; fmt = varfmt( dsid , j ) ; if missing( fmt ) then fmt = 'best12.'; rc=fput(fid,putc(putn(getvarn(dsid,j ),fmt ),'$char12.') ); end; end; rc = fwrite( fid ); end;
Getting around BOTH limitations – macro + PROC FCMP
Write out the tab-dlimited data for the 2nd thru jth variable
rc = fclose( fid ); rc = close( dsid ); rc = filename( 'clippy' ); return (0); endsub; quit; %local cmplib; %let cmplib=%sysfunc(getoption(cmplib)); options cmplib=(work.func); %put %sysfunc(c2cb(&lib,&mem)) ; options cmplib=(&cmplib); %mend copy2clipbrd;
Getting around BOTH limitations – macro + PROC FCMP Clean-up, end function and quit FCMP
rc = fclose( fid ); rc = close( dsid ); rc = filename( 'clippy' ); return (0); endsub; quit; %local cmplib; %let cmplib=%sysfunc(getoption(cmplib)); options cmplib=(work.func); %put %sysfunc(c2cb(&lib,&mem)) ; options cmplib=(&cmplib); %mend copy2clipbrd;
Getting around BOTH limitations – macro + PROC FCMP
Run the function
rc = fclose( fid ); rc = close( dsid ); rc = filename( 'clippy' ); return (0); endsub; quit; %local cmplib; %let cmplib=%sysfunc(getoption(cmplib)); options cmplib=(work.func); %put %sysfunc(c2cb(&lib,&mem)) ; options cmplib=(&cmplib); %mend copy2clipbrd;
Getting around BOTH limitations – macro + PROC FCMP
Restore cmplib option setting
and end the macro
Type the text you want to add to the menu
E&xport to Clipboard
Action
E&xport to Clipboard
It will appear on the menu as: Export to Clipboard
Getting around BOTH limitations – macro + PROC FCMP
Left click in the Action Command box
E&xport to Clipboard
Getting around BOTH limitations – macro + PROC FCMP
Type the action command you want SAS to apply
Action Command gsubmit '%%copy2clipbrd(%08b,%32b)';
gsubmit '%%copy2clipbrd(%08b,%32b)';
E&xport to Clipboard
Getting around BOTH limitations – macro + PROC FCMP
Click on OK to exit the Add Action screen
gsub ‘proc transpose data=%8b.%s(ob
E&xport to Clipboard
Getting around BOTH limitations – macro + PROC FCMP
Click on OK to exit the Table Options screen
Getting around BOTH limitations – macro + PROC FCMP
then, whenever you right-click on a file and select the option (i.e., press hot key (X) or point and left-click)
You can paste the file (Ctrl-V) into a workbook
and, if you want, you could add all of your repeated tasks to the SAS Explorer menu
Copy Variable Names to Clipboard
Run Proc Contents
Get descriptive statistics
Show all correlations
Compare groups
Print bar charts
Run factor analysis
http://www.sascommunity.org/wiki/User:Art297
The macro, paper and Powerpoint can be found at:
http://www.sascommunity.org/wiki/User:Art297
The macro, paper and Powerpoint can be found at:
A_Poor/Rich_SAS_Users_Proc_Export
Your comments and questions
are valued and encouraged
Contact the Authors
Arthur Tabachneck, Ph.D. myQNA, Inc. Thornhill, ON [email protected]
Tom Abernathy Pfizer, Inc. New York, NY [email protected]
Randy Herbison Westat Rockville, MD [email protected]
Matt Kastin I-Behavior, Inc. Penn Valley, PA [email protected]