SAS – Page 2 – Ryslander.com

ODS tagsets.ExcelXP

Creating files for Excel in SAS can be done in different ways. One of the ways is to use the ODS tagsets.ExcelXP. The code below shows how to use the ODS tagsets.ExcelXP. It creates an XML-file that conforms to the Microsoft XML Spreadsheet Specifications and can be opened with Microsoft Excel 2002 (also known as Excel XP) or later versions.
Compared to other ways of creating Excel-files in SAS the ODS tagsets.ExcelXP is very diverse and has a lot of options for doing extra things in your Excel-sheets – like frozen headers etc.
NB! One downside of using ODS tagsets.ExcelXP is that is do not support graphics.

The code below manually creates two sheets in the Excel-file, populates them with data through PROC REPORT (eg PROC PRINT can also be used) and gives two columns (variables) a different color.

ods listing close;

/*
This prevents the open and save dialog from showing when the Excel-file
is finished processing. This has to be used if you automatically want to 
convert the .XML file to .XLS or.XLSX.
*/
ods noresults;

ods tagsets.ExcelXP file="c:\output\ExcelFile.xml" style=Meadow
options(sheet_name="001_Sheet" autofilter='all'
frozen_headers='yes' width_fudge='.8' hidden_columns='');
        proc report data=sashelp.class nowd;
                columns _all_;
                compute Age;
                    call define(_COL_, 'style', 'style=[background=LightGray]');
                endcomp;
                compute Height;
                   call define(_COL_, 'style', 'style=[background=LightGray]');
                endcomp;
        run;

ods tagsets.excelxp options(sheet_name=
"002_Sheet" autofilter='all' frozen_headers='yes' width_fudge='.8' hidden_columns='');
        proc report data=sashelp.class nowd;
               columns _all_;
        run;

ods tagsets.excelxp close;
ods listing;

The Excel-XML files created can become very big, that’s why it’s sometimes a good idea to convert it to a native Excel-format like .XLS or .XLSX and still keep the changes made to the Excel-sheets. The macro below will convert the XML-files found in a folder to .XLS or .XLSX files using VBS (Visual Basic Script).
There can be a problem using the .VBS-script, if SAS is set to automatically start Excel to show the created file. The problem is that SAS is starting Excel to show the just created file, but also creates a lock on the file, that can collide with the .VBS-scrip trying to modify the file. This option for starting Excel can be removed in the SAS editor (Display Manager). Go to Tools -> Options -> Preferences -> Results (tab) and remove the tick from “View results as they are generated“. This will stop SAS from starting Excel when the .XML-file is created.

%
******************************************************************************************;
%*
%*  MACRO: CONVERT_FILES
%*
%*  USAGE: %convert_files(arg1=value, arg2=value, ... );
%*
%*  DESCRIPTION:
%*    This macro is used to convert files generated with ODS destinations
%*    to native Excel files. For more information see the below link.
%*    http://www.pharmasug.org/proceedings/2011/SAS/PharmaSUG-2011-SAS-TT02.pdf?page=12
%*
%*
%*
%*
%*******************************************************************************************;
options noxsync noxwait;
/* Example: %ConvertXML(Default=&SASProgramPath.\output\,
Store=&SASProgramPath.\output\temp.vbs); */
%macro ConvertXML(default=c:\temp\convert, store=c:\temp\, ext=xml);
  data _null_;
     file "&store";
     put " Dim oXL ";
     put " Dim oFolder";
     put " Dim aFile";
     put " Dim FSO";
     put " Set oXL = CreateObject(""Excel.Application"")";
     put " Set FSO = CreateObject(""Scripting.FileSystemObject"")";
     put " oXL.DefaultFilePath = ""&default""";
     put " oXL.DisplayAlerts = False";
     put "  if FSO.FolderExists(oXL.DefaultFilePath) then";
     put "   Set oFolder = FSO.GetFolder(oXL.DefaultFilePath)";
     put "     For each aFile in oFolder.Files ";
     put "       If Right(LCase(aFile.Name), 4) = "".&ext"" Then";
     put "        oXL.Workbooks.Open(aFile.Name)";
     put "        oXL.Visible = false";
     put "        if (oXL.Version) >= 12 Then" ;
     put "        oXL.ActiveWorkBook.SaveAs Left(aFile, Len(aFile) -4) & "".xlsx"",51";
     put "    Else";
     put "       oXL.ActiveWorkBook.SaveAs Left(aFile, Len(aFile) -4) & "".xls"",-4143";
     put "   End If";
     put " oXL.ActiveWorkBook.Close SaveChanges = True";
     put " End If";
     put " Next";
     put " Set oFolder = Nothing";
     put " end if";
     put " FSO.DeleteFile(""&default\*.&ext""), DeleteReadOnly";
     put " oXL.DisplayAlerts = True";
     put " oXL.Quit";
     put " Set oXL = Nothing";
     call system("&store");
 run;
%mend;

If you don’t want to remove the tick in “View results as they are generated” a little change in the program above is possible.

%
******************************************************************************************;
%*
%*  MACRO: CONVERT_FILES
%*
%*  USAGE: %convert_files(arg1=value, arg2=value, ... );
%*
%*  DESCRIPTION:
%*    This macro is used to convert files generated with ODS destinations
%*    to native Excel files. For more information see the below link.
%*    http://www.pharmasug.org/proceedings/2011/SAS/PharmaSUG-2011-SAS-TT02.pdf?page=12
%*
%*
%*
%*
%*******************************************************************************************;
options noxsync noxwait;
/* Example: %ConvertXML(Default=&SASProgramPath.\output\,
Store=&SASProgramPath.\output\temp.vbs); */
%macro ConvertXML(default=c:\temp\convert, store=c:\temp\, ext=xml);
  data _null_;
     file "&store";
     put " Dim oXL ";
     put " Dim oFolder";
     put " Dim aFile";
     put " Dim FSO";
     put " Set oXL = CreateObject(""Excel.Application"")";
     put " Set FSO = CreateObject(""Scripting.FileSystemObject"")";
     put " oXL.DefaultFilePath = ""&default""";
     put " oXL.DisplayAlerts = False";
     put "  if FSO.FolderExists(oXL.DefaultFilePath) then";
     put "   Set oFolder = FSO.GetFolder(oXL.DefaultFilePath)";
     put "     For each aFile in oFolder.Files ";
     put "       If Right(LCase(aFile.Name), 4) = "".&ext"" Then";
     put "        oXL.Workbooks.Open(aFile.Name)";
     put "        oXL.Visible = false";
     put "        if (oXL.Version) >= 12 Then" ;
     put "        oXL.ActiveWorkBook.SaveAs Left(aFile, Len(aFile) -4) & "".xlsx"",51";
     put "    Else";
     put "       oXL.ActiveWorkBook.SaveAs Left(aFile, Len(aFile) -4) & "".xls"",-4143";
     put "   End If";
     put " oXL.ActiveWorkBook.Close SaveChanges = True";
     put " End If";
     put " Next";
     put " Set oFolder = Nothing";
     put " end if";
     put " FSO.DeleteFile(""&default\*.&ext""), DeleteReadOnly";
     put " oXL.DisplayAlerts = True";
     put " oXL.Quit";
     put " Set oXL = Nothing";
     *call system("&store");
 run;

x "cscript ""&store""";
%mend;

You replace the call system with and x-command. If you use this option the XML-files created will still be locked and can therefore not be deleted. But it is possible to create the .XLS or.XLSX file.

SYSCC

SYSCC contains the current condition code that SAS returns to your operating environment and it can be used to check the error-state of your SAS-program.
But SYSCC doesn’t catch all SAS errors. If you want it to catch more SAS errors you need to set the option ERRORCHECK=STRICT.

options errorcheck=strict;

Setting this option will catch errors in libname, filename and include statements in SAS.

Finding unique and dublicates in SAS

The code below shows you how to find unique and duplicate values in a dataset and get them seperated into two different datasets.
The variables you want to examin for uniqueness has to be in the by-statement and each have an not(first.<variable> and last.variable). Be aware that in SAS 9.3 there is an easier solution using proc sort.

data unique dups;
 set sashelp.class;
 by Age Height Name Weight;
 if not(first.Age and last.Age) 
 and not(first.Height and last.Height) 
 and not(first.Name and last.Name)
 and not(first.weight and last.Weight) then output dups;
 else output unique;
run;

This code is different than using proc sort prior to SAS 9.3

proc sort data=sashelp.class nodupkey out=unique dupout=dups;
 by Age Height Name Weight;
run;

The code above will take the first of the dublicates and put it into the unique-dataset. It will not completely seperate unique and duplicate rows from each other.

In SAS 9.3 proc sort has a new parameter uniqueout. This can be used to do the trick of the datastep much easier. I haven’t tried it, but I imagine that this is how it works.

proc sort data=sashelp.class nouniquekeys uniqueout=singles out=dublet; 
 by Age Height Name Weight;
run;

Copy .sas files (program files) with SAS

The code below copies all the SAS-programs (*.sas) files in a directory to another directory. This solution should be used if you don’t want to use an OS-command that copies the files. Using an OS-command is a lot easier and doesn’t require as much code. But of course depends on the OS your running on. This solution is OS independent.

%let SourcePath = C:\test\source;
%let DestinationPath = C:\test\destination;

/*
Reads the .sas files in &location.
*/
%macro GetFilenames(location);
 filename _dir_ "%bquote(&location.)";
 data filenames(keep=memname);
  handle=dopen( '_dir_' );
  if handle > 0 then do;
   count=dnum(handle);
   do i=1 to count;
    memname=dread(handle,i);
    output filenames;
   end;
  end;
  rc=dclose(handle);
 run;

 filename _dir_ clear;
%mend;

%GetFilenames(&SourcePath.);

/* We only want SAS-files and not SAS-datasets. */
data filenames;
 set filenames;
 if index(memname, '.sas') eq 0 or index(memname, '.sas7bdat') eq 1 then delete;
run;

%let Delimitor = ¤;

proc sql noprint;
 select memname into :Files separated by "&Delimitor"
 from FileNames;
quit;

%put Files to copy: &Files;

%macro CopySASFiles;
 %let NumberOfFiles = %sysfunc(countw(&Files., &Delimitor.));
 %put Number of files: &NumberOfFiles.;

 %do J=1 %to &NumberOfFiles.;
  %let File = %scan(&Files., &J, &Delimitor.);
  %put Copying SAS-program: &File.;

  data _null_;
   infile "&SourcePath.\&File."  lrecl=32767;
   file "&DestinationPath.\&File.";
   input;
   put _infile_;
  run;
 %end;
%mend;
%CopySASFiles;

Incremental/Continuous variable in a dataset

The code below makes a incremental/continuous variable called ID in a dataset.

data test;
 ID + 1;
 set test;
run;

Using SAS display manager (DM) for data exploration

The display manager (also known as DM) in base-SAS can be used for data exploration. For this demonstration the dataset sashelp.class will be used. Now sashelp.class is easy to get an overview of because it only has five variables. But if you have a lot of variables then some kind of data exploration/data manipulation might be handy.

Let’s say that you would like to take a look at the varable Age. In the DM you will write keep age.

This will result in a displaying of the data only showing the variable age. Now you would like to have all the other variables shown but keep age as the first variable being displayed. This can be done writing the command unide _all_ in the DM.

Now all the variable will be shown with age as the first variable.

It is also possible to keep multiple variables. In the DM you can eg. write keep ‘height weight’. Remember that when keeping multiple variables you will have to write the variables in ‘ ‘.

Now only these variables will be shown.

You can again unhide the rest of the variables writing unhide _all_ this will keep the height and weight variables as the first variables being shown.

Get SQL recipe for a dataset in SAS

The code below will make a file class.sql containing the SQL-code for creating the dataset sashelp.class

ods tagsets.sql file="class.sql";
 proc print data=sashelp.class ;
 run;
ods _all_ close;

The file will look something like this

Create table CLASS
(Name varchar(7), Sex varchar(1), Age float, Height float, Weight float);
Insert into CLASS(Name, Sex, Age, Height, Weight)
Values (‘Alfred’, ‘M’, 14, 69.0, 112.5);
Insert into CLASS(Name, Sex, Age, Height, Weight)
Values (‘Alice’, ‘F’, 13, 56.5, 84.0…

Making an empty dataset in SAS

The code below shows you how to make an empty dataset in SAS.
If you omit the if-sentence and below, then you will get an empty row in the dataset.

data StatusTable;
 length Dataset $100 Message $150 Status $10;
 call missing(Dataset, Message, Status);
 if _N_ = 0 then output;
 stop;
run;

This can also be done a bit easier in SQL.

proc sql noprint;
create table Dataset
(
Dataset char 100,
Message char 150,
Status char 10
);
quit;

Comparing datasets in SAS

The code below compares two datasets. It merges them together and makes three datasets. One dataset contains identical observations from the two datasets. The second dataset contains observations only found in one dataset. And the third dataset contains observations only found in the other dataset.

data InBoth InOne InTwo;
merge One (in=a) Two (in=b);
by <variables>;
if a and b then
 output InBoth;
if a and not b then
 output InOne;
if b and not a then
 output InTwo;
run;

A lot of good SAS macros

The link below has a lot of good SAS macros.

http://sas.cswenson.com/