Two awesome Enterprise Guide tricks from Angela Hall SAS

Two awesome Enterprise Guide tricks

Direct Source Link

SAS Macro to put SAS data into Multiple Excel Work sheets

I believe most of us have encountered this situation...
I have a sas dataset with more than 70k records...How can I provide this information in an Excel file to the business user....


If one is using previous versions of MS Excel ie. < Excel 2007 then one can provide the information in multiple excel sheets in an Workbook...or put it to ,(comma) or a | (pipe) delimited file and then open that with notepad/word application.... 

if you still wanted to stick with Excel format...then split the dataset to ~65000 rows and load it multiple work sheets in a workbook....

Try with this macro example where I have created a dataset with 1 million records.....This macro then splits the dataset at 65000 records and then dumps the data to multiple worksheets in the Example.xls file....

ExcelXP tagsets are used here that allow us to write to multiple worksheets in a work book....

Inorder to try this example you will have to download this zip file from SAS @ http://support.sas.com/rnd/base/ods/odsmarkup/tagsets_9.1.zip

and then find and extract excltags.tpl file and put it at your C:\ or a location of your choice (In that case please update your location at this statement %include "C:\excltags.tpl"; )


Here's the code then....

options nosource nomprint nomlogic nosymbolgen;

/**

Usage of the Macro-

%splitdsnbyobs(DatasetName, No ofobservation to split by)

**/

/* creating a dataset with 100000 observations*/

%macro splitdsnbyobs(dsn,splitby,ExlFilename,ExlFilepath);

data _null_;

      set &dsn nobs=num;

      call symput('no_obs',num);

run;

%let no_obs=&no_obs; /*Get the number of observations in &dsn*/

%include "C:\excltags.tpl";

ods listing close;

ods tagsets.excelxp path="&ExlFilepath" file="&ExlFilename" style=printer

      options( embedded_titles='yes' embedded_footnotes='yes' suppress_bylines='no');

/* keep the observations from firstobs= and obs=*/

%do i=1 %to %sysfunc(ceil(&no_obs/&splitby));

      data &dsn.&i.;

      set &dsn (firstobs=%sysfunc(floor(%eval((&i.-1)*&splitby.+1))) obs=%sysfunc(ceil(%eval(&i * &splitby.))));

      run;

      ods tagsets.excelxp options(sheet_interval='none' sheet_name="&dsn.&i.");

      proc print data=&dsn.&i.; run;

%end;

ods tagsets.excelxp close;

ods listing;

%mend splitdsnbyobs;

/* Eg. Create a Dsn with 1000000 observations */

data loops;

do i=1 to 1000000;

      output;

end;

run;

/*Now call the macro to split the observations every 65000 records*/

%splitdsnbyobs(loops,65000,Example.xls,C:\);

Some useful links on this topic


http://support.sas.com/rnd/base/ods/odsmarkup/excelxp_help.html
http://support.sas.com/rnd/base/ods/odsmarkup/
http://support.sas.com/rnd/base/ods/odsmarkup/tagsets_9.1.zip
http://support.sas.com/kb/32/394.html
http://support.sas.com/rnd/base/ods/odsmarkup/excelxp_demo.html

SAS Macro to delete a SAS dataset

Here’s a simple SAS Macro to delete a SAS dataset…It takes the name of the SAS dataset as the parameter…

view source

print?

%macro deletedsn(dsname);

%if %index(&dsname,.) eq 0  %then %do; %let lib=work; %let dsn=&dsname; %end;

%else %if %index(&dsname,.) gt 0  %then %do; %let lib=%scan(&dsname,1,.); %let dsn=%scan(&dsname,2,.); %end;

proc datasets lib=&lib nolist;

delete &dsn;

quit;

%mend deletedsn;

%deletedsn(new);

%deletedsn(somelib.new);

Redirecting your SAS LOG and OUTPUT to external files

The PRINTTO procedure defines destinations for SAS procedure output and for the SAS log. By default, SAS procedure output and the SAS log are routed to the default procedure output file and the default SAS log file for your method of operation. See Default Destinations for SAS Log and Procedure Output. You can store the SAS log or procedure output in an external file or in a SAS catalog entry. With additional programming, you can use SAS output as input data within the same job.

PROC PRINTTO;

To do this	Use this option
provide a description for a SAS log or procedure output stored in a SAS catalog entry	LABEL=
route the SAS log to a permanent external file or SAS catalog entry	LOG=
combine the SAS log and procedure output into a single file	LOG= and PRINT= with same destination
replace the file instead of appending to it	NEW
route procedure output to a permanent external file or SAS catalog entry or printer.	PRINT=

view source

print?

filename logf "C:\Documents and Settings\SASTechies\Desktop\log.txt";

proc printto log=logf new; /*redirecting the log to log.txt*/

run;

data numbers;

input x y z;

datalines;

14.2   25.2   96.8

10.8   51.6   96.8

9.5   34.2  138.2

8.8   27.6   83.2

11.5   49.4  287.0

6.3   42.0  170.7

;

proc printto print='C:\Documents and Settings\SASTechies\Desktop\output.txt' new; /*redirecting the output to output.txt*/

run;

proc print data=numbers;

title 'Listing of NUMBERS Data Set';

run;

/* You can also combine the SAS log and procedure output into the same/single file.

Note: that you should NOT have the NEW option in this case or else it would delete the contents of the files everytime there is a new line in the log/output

*/

filename combined "C:\Documents and Settings\SASTechies\Desktop\combined.txt";

proc printto print=combined; /*redirecting the output to combined.txt*/

run;

proc printto log=combined; /*redirecting the output to combined.txt*/

run;

proc print data=numbers;

title 'Listing of NUMBERS Data Set';

run;

proc printto;run; /* Re-routing the log and output destinations to the default locations */