SAS – Ryslander.com

Installing and configuring an SAS OLEDB-driver for MSSQL

To get the correct SAS OLEDB driver or newest SAS OLEDB driver. It is best to do a search e.g. on Google.
NB! You need an account at SAS to be able to download.

And even thou this guide for Installing and configuring an SAS OLEDB-driver for MSSQL is old – it is still very useful.

Be aware, that it is possible to code a program in e.g. .NET that reads a SAS-dataset. It can be done with the SasReader (currently in version 1.0.6).

Below code in C# reads a SAS-dataset and outputs it into a .CSV-file.
Credits to my colleague that figured this out.

using System;
using System.IO;
using SasReader;
using System.Text;
 
namespace SasToCsvConverter
{
    class Program
    {
        static void Main(string[] args)
        {
           // Define paths
           // string sasFilePath = @"C:\temp\<YOUR SAS-DATASET>.sas7bdat";
           // string csvFilePath = @"C:\temp\output.csv";
            try
            {
                // Initialize SAS file reader
                using (FileStream sasToParseFileInputStream = File.OpenRead(sasFilePath))
                {
                    SasFileReader sasFileReader = new SasFileReaderImpl(sasToParseFileInputStream);
 
                    // Open the CSV file for writing
                    using (var writer = new StreamWriter(csvFilePath, false, Encoding.UTF8))
                    {
                        // Read and write META DATA
                        var sasMetaColumns = sasFileReader.getColumns();
 
                        // Write header
                        var headerNames = new StringBuilder();
                        foreach (var column in sasMetaColumns)
                        {
                            headerNames.Append(column.getName()).Append(",");
                        }
 
                        // Remove the trailing comma
                        writer.WriteLine(headerNames.ToString().TrimEnd(','));
 
                        // Write DATA
                        long rowCount = sasFileReader.getSasFileProperties().getRowCount();
                        for (int i = 0; i < rowCount; i++)
                        {
                            var row = sasFileReader.readNext(); // object[]
                            var rowValues = new StringBuilder();
                            foreach (var value in row)
                            {
                                var stringValue = value?.ToString() ?? string.Empty;
                                rowValues.Append(EscapeCsvValue(stringValue)).Append(",");
                            }
 
                            // Remove the trailing comma
                            writer.WriteLine(rowValues.ToString().TrimEnd(','));
 
                            // Optional: Log progress
                            Console.WriteLine($"Processed row {i + 1}/{rowCount}");
                        }
                    }
                }
 
                Console.WriteLine("Conversion to CSV completed successfully.");
            }
 
            catch (Exception ex)
            {
                Console.WriteLine($"An error occurred: {ex.Message}");
            }
        }
 
        private static string EscapeCsvValue(string value)
        {
            if (value.Contains(",") || value.Contains("\"") || value.Contains("\n"))
            {
                return $"\"{value.Replace("\"", "\"\"")}\"";
            }
            return value;
        }
    }
}

Extending the Display Manager in SAS

The commands below can be used to expand the Display Manager in SAS.

Command	Description
vt &syslast.;	Add to keys, e.g. F5. This opens a Viewtable with the latest run dataset/view.
next viewtable:; end;	Add to keys, e.g. F9. Then F9 closes the last used table (that is open). Can close all views with multiple F9’s.
odsresults; select all; clear; wpgm;	Add to keys, e.g. SHIFT F1. This key bind clears all SAS “results” and returns to the program editor.
log; clear; wpgm;	Add to keys, e.g. F4. Clears log without having to highlight the log window. Returns to the program editor.

Info about SAS-datasets in the WORK-library

In SAS Enterprise Guide it is not very easy to see the size and number of observations in datasets in the WORK-library.

The macro below looks in the DICTIONARY.TABLES and gets these info for the WORK-library. Be aware that it will not work for views, because it’s not doing and actual count of the SAS-datasets.

/********************************************************************************
Author        : 
Creation date : 
Description   : Gets info about datasets in the WORK-library.
Example       : %countwork(print);
*********************************************************************************
Input
-----
&print        : If not empty it will do a PROC PRINT of the dataset WORKDS created
                by the macro.
*********************************************************************************
Output
------
WORKDS        : Contains information about the datasets in the WORK-library.
********************************************************************************/
%macro CountWork(print);
          proc sql;
                   create table workds as
                             select    libname
                                                , memname
                                                , typemem
                                                , nobs format=commax10.0
                                                , filesize format=sizekmg.
                                                , nvar
                             from dictionary.tables
                             where libname eq 'WORK'
                             order by nobs
                   ;
          quit;
 
          %if &print. ne %then
          %do;
                   proc print data=workds;
                   run;
          %end;
%mend;

Microsoft Team Foundation Server (TFS) and SAS

Below is shown how you can extract the current Microsoft Team Foundation Server (TFS) revision number for a given file into SAS and use it in your SAS program.

Be aware that Visual Studio 13 is used in the example below. It is uncertain if newer versions of Visual Studio will work.

%macro TFSRev(filename);

%let tfsver1=; %let tfsver2=; %let tfsver3=; /* The default path for the TF program tf.exe with Visual Studio 13. */
%let TFRev=C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\tf.exe;

filename tfver pipe “call “”&TFRev”” hist “”&filename”” “;
data _null_;
infile tfver;
input; /* The output is in three different lines. */
put _n_ _infile_; /* Puts the three different output lines in three diffent macrovariables called tfsver1, tfsver2 and tfsver3. */
call symput(‘tfsver’ !! put(_n_,1.), _infile_);
run;

/* Suppress output from SAS. */ options nosource;
%put Extracting TFS information from: &filename;
%put; %put &tfsver1; %put &tfsver2; %put &tfsver3; option source;

%mend;
%TFSRev(C:\TEMP\sasprogram.sas);

You can now use the three diffent macrovariables called tfsver1, tfsver2 and tfsver3 in your program.

Microsoft Visual Studio Team Foundation Server 2013 Power Tools might be needed.
https://marketplace.visualstudio.com/items?itemName=TFSPowerToolsTeam.MicrosoftVisualStudioTeamFoundationServer2013Power

You might also want to have a look at this blogpost regarding SVN.

Automatically analyzing and documenting SAS-code

In SAS Enterprise Guide and SAS-DI you have the possibility to analyze the code for a SAS-program.

The picture below shows the Analyze Program option in SAS Enterprise Guide.

Analyzing the code should result in a conversion of the SAS-code to a SAS Enterprise Guide flow or SAS-DI flow. But none of these code analyzers are very good. Depending on the complexity of the SAS-code you put into the analyzer, they will leave you with a more or less successful conversion. And more times than not they will fail at doing the job.

But SAS comes with a procedure PROC SCAPROC that does a really good job at analyzing and documenting SAS-code.

Below is an example. The ‘attr‘ option writes additional information about the variables in the input data sets and views. The ‘expandmacros‘ option expands macro invocations into separate tasks.

proc scaproc;
record '<PATH>' attr expandmacros;
run;
 
proc scaproc;
write;
run;

Be aware, that PROC SCAPROC is not able to get information from the execution of your SAS-program flow, if you are rsubmitting to other servers.

The links below gives you further descriptions of PROC SCAPROC and its options. There’s also a guide on how to do a graphical presentation of the result fra PROC SCAPROC. http://support.sas.com/resources/papers/proceedings17/1104-2017.pdf

Overview of the SCAPROC Procedure
http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a003199745.htm

Program for parsing the output from PROC SCAPROC to create a data set with inputs and outputs.
http://support.sas.com/kb/58/047.html

Innovative Performance Improvements Through Automated Flowcharts In SAS
http://support.sas.com/resources/papers/proceedings16/11580-2016.pdf

Uniqueness in data

The SAS-macro below will tell you if a variable in a dataset is unique.

/********************************************************************************
Author        : 
Creation date : ddmmmyyy
Description   : Gets info about uniqness in a SAS-dataset.
Example       : %uniq(sashelp.class, name, print)
*********************************************************************************
Input
-----
&datset   : The dataset to test.
&variable : The variable to test for uniqueness.
&print    : If the output/result should be shown in a PROC PRINT.
*********************************************************************************
Output
------
freq_result          : Dataset sorted with doublets as first rows.
freq_result_doublets : Data containing only the doublets.
********************************************************************************/
%macro uniq(dataset, variable, print);
	proc freq data=&dataset.;
		tables &variable / noprint out=freq_result;
	run;

	proc sort data=freq_result;
		by descending count;
	run;

	data freq_result_doublets;
		set freq_result;
		where count gt 1;
	run;

	proc sql noprint;
		select count(*) into :doublets
		from freq_result_doublets
		where count gt 1
		;
	quit;

	%put --------------------------------------------------------------------------------------------;
	%put NUMBER OF DOUBLETS IN [%upcase(&dataset.)] FOR VARIABLE [%upcase(&variable.)]: &doublets.;
	%put --------------------------------------------------------------------------------------------;

	%if &doublets. eq 0 %then
	%do;
		%put !!!! NO DOUBLETS !!!;
	%put --------------------------------------------------------------------------------------------;
	%end;

        %if &print. ne %then
        %do;
             proc print data=freq_result;
             run;
        %end;
%mend;

SAS crashes when you connect with RDP

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

Getting information about your SAS-installation

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

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;

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 Encode Password

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. See this blogpost of how to decode BASE64 directly in SAS.

Be aware of this website (Thanks Dmitriy for the comment below). That shows how to decode all passwords encoded with the above methods in SAS. And this older website, not showing the exact method but outlining the concept of how to do it.
If the website is not available, I have saved the webpage as pdf.

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);

Reading data from api.statbank.dk into SAS

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;