Category Archives: SAS

SAS

Directory for SAS-macros

March 7, 2018 Root Leave a comment

The code below will give you the path of all the directories containing macros in the SASAUTOS in your SAS-session.

%put %sysfunc(getoption(sasautos));
filename _all_ list;

%put %sysfunc(getoption(sasautos));

filename _all_ list;

SAS

SAS crashes when you connect with RDP

March 17, 2015 Root Leave a comment

If your SAS-session crashes when you connect with RDP to a server the root-cause could be a check that SAS does for available printers.

To avoid these crashes it is possible to set a parameter in the config-file for SAS. The main config-file for SAS is called SASv9.cfg and in SAS 9.2 it’s usually located in C:\Program Files\SAS\SASFoundation\9.2\nls\en.

The parameter you have to add to the config-file is:
-noqueryports

SAS

Quote macrovariables e.g. for an IN SQL-statement

November 7, 2014 Root Leave a comment

The code below lets you qoute the contant of a macrovariables e.g. to be used in an IN SQL-statement.

%let Variables = age height weight;
%let InStatement = %Str(%’)%sysfunc(Tranwrd(&Variables.,%Str( ),%Str(%’, %’)))%Str(%’);

%let Variables = age height weight;

%let InStatement = %Str(%’)%sysfunc(Tranwrd(&Variables.,%Str( ),%Str(%’, %’)))%Str(%’);

The macrovariable InStatement will contain ‘age’,’height’,’weight’. And can then be used in an IN SQL-statement.

SAS

Getting programname for the SAS-program being executed

November 6, 2014 Root Leave a comment

The code below will get the programname of the program being used.

%macro GetSASProgramName;
	%scan(%sysget(SAS_EXECFILEPATH),-1,"\");
%mend;
%let SASProgramName = %GetSASProgramName;

%macro GetSASProgramName;

%scan(%sysget(SAS_EXECFILEPATH),-1,"\");

%mend;

%let SASProgramName = %GetSASProgramName;

This will only work on Windows.

SAS

Getting information about your SAS-installation

September 23, 2014 Root Leave a comment

Below is a couple of commands that can be used to get information about your SAS-installation.

proc setinit;
run;

1 2	proc setinit; run;

The Setinit-command will give you information about the SAS-products that your SAS-installation is licensed for.

proc product_status;
run;

1 2	proc product_status; run;

The Product_status-command will give you information about the SAS-products installed on your system.
Keep in mind that there could be a difference between what’s licensed on your system and what’s actually installed.
The commands above will only give you information about the products installed for base-SAS. If you want information about what other SAS-products is installed on you system, then you can get this SAS-program from SAS and run it on your system.

SAS

Make a folder with ISO date and time in SAS

July 17, 2014 Root Leave a comment

The code below makes a folder with an ISOdate.

%let ISOdt  = %sysfunc(datetime(), B8601DT.);

data _null_;
 rc = system("mkdir c:\test\&ISOdt.");
 put rc=;
run;

%let ISOdt = %sysfunc(datetime(), B8601DT.);

data _null_;

rc = system("mkdir c:\test\&ISOdt.");

put rc=;

run;

SAS

SAS Encode Password

May 8, 2014 Root 1 Comment

The code below lets you encode a password in SAS, based on the methods for encoding passwords available in SAS.
You can see a description of the methods for encoding below. Be aware that a password encoded with base64 (sas001) can be very easily discovered.

Encoding vs. encryption
PROC PWENCODE uses encoding to disguise passwords. With encoding, one character set is translated to another character set through some form of table lookup. Encryption, by contrast, involves the transformation of data from one form to another through the use of mathematical operations and, usually, a “key” value. Encryption is generally more difficult to break than encoding. PROC PWENCODE is intended to prevent casual, non-malicious viewing of passwords. You should not depend on PROC PWENCODE for all your data security needs; a determined and knowledgeable attacker can decode the encoded passwords.

Encoding is a process of converting one set of meaningful characters into another set. By converting into a different set the characters become unreadable and the meanings of the characters are disguised from the public.
Encryption is a method to transform data from plain text to cipher text through the use of a mathematical algorithmic scheme. Any plain text through encryption process becomes cipher text and is illegible to anyone without a special key.
Though both processes involve converting data from one format to another, encoding process is designed for disguising the data to be revealed casually. Encoding and decoding processes do not require a special key. On the other hand, encryption method is used to protect data from to be revealed to anyone other than the intended recipient. In order to read the encrypted text both the encryption key and the mathematical algorithm are required.
Encoding is intended to disguise data from to be revealed in public. For our purpose, it works well to prevent casual, non-malicious viewing of password in the SAS programs. Because of the special key and mathematical algorithm involved encryption is generally more difficult to break. It is designed for maintaining data confidentiality. Encoding and encryption are developed for different purposes. One should not replace another.

The code below saves a file to drive containing a %let statement with the encoded password.

%macro EncodePwd(SavePath, Method);

        filename clp clipbrd;

        proc pwencode in="&Password." out=clp method=&Method.;
        run;

        data _null_;
            infile clp length=len;
            length encodedPW $100;
            input ;
            encodedPW = substr(_infile_, 1, len); /* Remove linefeed */
            call symput('encodedPW', compress(encodedPW));
        run;
        filename clp clear;

        filename encoded "&SavePath.";
        data _NULL_;
                file encoded(pwd.sas);
                tmp='%let pw='||"&encodedPW"||';';
                put tmp;
        run;
        filename encoded clear;
%mend;
%EncodePwd(C:\, sas001);
%EncodePwd(D:\, sas002);

%macro EncodePwd(SavePath, Method);

filename clp clipbrd;

proc pwencode in="&Password." out=clp method=&Method.;

run;

data _null_;

infile clp length=len;

length encodedPW $100;

input ;

encodedPW = substr(_infile_, 1, len); /* Remove linefeed */

call symput('encodedPW', compress(encodedPW));

run;

filename clp clear;

filename encoded "&SavePath.";

data _NULL_;

file encoded(pwd.sas);

tmp='%let pw='||"&encodedPW"||';';

put tmp;

run;

filename encoded clear;

%mend;

%EncodePwd(C:\, sas001);

%EncodePwd(D:\, sas002);

SAS

Reading data from api.statbank.dk into SAS

March 24, 2014 Root Leave a comment

The code below lets you read data from api.statbank.dk into SAS. api.statbank.dk is an api provided by Statistics Denmark (Danmarks Statistik) for reading their public data.

The code below gets it’s data from the table FOLK1. You can get a list of avaliable tables from this url – http://api.statbank.dk/v1/tables?format=CSV. The code also sets some filters on the tables, some of the information used in this post comes from here.

filename dst
url
'http://api.statbank.dk/v1/data/Folk1/CSV?valuePresentation=Value&Tid=*&K%C3%B8n=*&Omr%C3%A5de=*&alder=*'
                        proxy='http://<your proxy:and port>/'
                        debug
                        lrecl=8192
                        puser="<your userid to proxy>"
                        ppass="<your password to proxy>";

data test;
        infile dst dlm=';' encoding="utf-8";
        format Quarter Sex Municipality Age $50.;
        input Quarter $ Sex $ Municipality $ Age $ Count;
run;

filename dst

url

'http://api.statbank.dk/v1/data/Folk1/CSV?valuePresentation=Value&Tid=*&K%C3%B8n=*&Omr%C3%A5de=*&alder=*'

proxy='http://<your proxy:and port>/'

debug

lrecl=8192

puser="<your userid to proxy>"

ppass="<your password to proxy>";

data test;

infile dst dlm=';' encoding="utf-8";

format Quarter Sex Municipality Age $50.;

input Quarter $ Sex $ Municipality $ Age $ Count;

run;

SAS

Reading multiple sheets from Excel in SAS

March 20, 2014 Root Leave a comment

The code below let’s you import the sheets found in an Excel-file to SAS datasets.

/*
ExcelFile: The Excell-file containing the sheets that you want to import.
Prefix: Prefix of the datasets imported into work.
*/

%macro ReadExcelSheets(ExcelFile=, Prefix=, OutLib=work);

     libname XLData "&ExcelFile." access=readonly;

     %let Delimitor = ¤;

     proc sql noprint;
       select compress(memname,"$") into :Sheets separated by '&Delimitor.'
             from dictionary.tables
             where libname = "XLDATA" and upcase(memname) like '%$'
       ;
     quit;

     %put Sheets          : &Sheets.;
     %let NumberOfSheets = %sysfunc(countw(&Sheets., &Delimitor.));
     %put Number of sheets: &NumberOfSheets.;

     %do J=1 %to &NumberOfSheets.;
         %let Sheet = %scan(&Sheets., &J, &Delimitor.);
         %put Processing: &Sheet.;

         %if %length(&Prefix) %then %let Prefix = &Prefix._;
         proc import out=&OutLib..&Prefix.&Sheet. datafile="&ExcelFile."
                dbms=xls replace;
                sheet="&Sheet.";
                getnames=yes;
         run;

     %end;

     libname XLData clear;
%mend ReadExcelSheets;

%ReadExcelSheets(ExcelFile=c:\temp\excelfile.xls, Prefix=test);

ExcelFile: The Excell-file containing the sheets that you want to import.

Prefix: Prefix of the datasets imported into work.

%macro ReadExcelSheets(ExcelFile=, Prefix=, OutLib=work);

libname XLData "&ExcelFile." access=readonly;

%let Delimitor = ¤;

proc sql noprint;

select compress(memname,"$") into :Sheets separated by '&Delimitor.'

from dictionary.tables

where libname = "XLDATA" and upcase(memname) like '%$'

;

quit;

%put Sheets : &Sheets.;

%let NumberOfSheets = %sysfunc(countw(&Sheets., &Delimitor.));

%put Number of sheets: &NumberOfSheets.;

%do J=1 %to &NumberOfSheets.;

%let Sheet = %scan(&Sheets., &J, &Delimitor.);

%put Processing: &Sheet.;

%if %length(&Prefix) %then %let Prefix = &Prefix._;

proc import out=&OutLib..&Prefix.&Sheet. datafile="&ExcelFile."

dbms=xls replace;

sheet="&Sheet.";

getnames=yes;

run;

%end;

libname XLData clear;

%mend ReadExcelSheets;

%ReadExcelSheets(ExcelFile=c:\temp\excelfile.xls, Prefix=test);

SAS

ODS tagsets.ExcelXP

March 7, 2014 Root 2 Comments

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;

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;

******************************************************************************************;

%* 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;

******************************************************************************************;

%* 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.

Ryslander.com

Category Archives: SAS

Directory for SAS-macros

SAS crashes when you connect with RDP

Quote macrovariables e.g. for an IN SQL-statement

Getting programname for the SAS-program being executed

Getting information about your SAS-installation

Make a folder with ISO date and time in SAS

SAS Encode Password

Reading data from api.statbank.dk into SAS

Reading multiple sheets from Excel in SAS

ODS tagsets.ExcelXP

implementation, consolidation and innovation