Below SAS-code will let you decode BASE64-encoded text directly in SAS.
%let id_all ='<BASE64 ENCODED TEXT>';
%let session_id = %sysfunc(inputc(%scan(&id_all,1,':')===,$base64x80));
%put &=session_id;
SAS uses BASE64-encoding in PROC PWENCODE for SAS001-encoding.
See this blogpost for information about PROC PWENCODE.
Below is a step-by-step guide to install and configure an ODBC-driver for SAS SPD-server.
If this is to be used by SSIS, then you will need to create these as 32-bit ODBC-connections. SSIS it not able (as of November 2021) to easily handle 64-bit ODBC-connections.
Below example is for the SPDPROD SPD-server library on the SPDSERVER01 server
On the Server-tab
| Name | <SERVER NAME>.<PORT> e.g. SPDSERVER01.5180 |
Press the Configure-button (in the dialog above)
| Server Address | <SERVER NAME> e.g. SPDSERVER01 |
| User Name | <I THINK THIS MUST BE THE USERNAME FOR USED FOR YOUR DBQ> |
| User Password | < THE USER PASWWORD FOR USER USED ABOVE> |
| Connection Options | DBQ='<FOUND IN YOUR SPD-SERVER CONFIGURATION>’ HOST='<SERVER>’ SERV='<PORT>’ E.g. DBQ='<FOUND IN YOUR SPD-SERVER CONFIGURATION>’ HOST=’SPDSERVER01′ SERV=’5180′ |
On the Libraries-tab
| Name | <NAME OF THE LIBNAME ON THE SPD-SERVER TO BE ACCESSED> |
| Hostfile | <NAME OF THE LIBNAME ON THE SPD-SERVER TO BE ACCESSED> |
| Description | <DESCRIPTION> |
| Engine | SPDSENG |
| Options | DBQ='<FOUND IN YOUR SPD-SERVER CONFIGURATION>’ |
If you do not have a tool to test a ODBC-connection available on the machine for the installation. It is possible to test these through PowerShell.
This is possible using the PowerShell code below.
$conn = new-object System.Data.Odbc.OdbcConnection
$conn.connectionstring = "DSN=<NAME OF YOUR ODBC-CONNECTION>"
$conn.open()
$sql = "SELECT * FROM <TABLE IN LIBNAME ON SPD-SERVER>";
$cmd = New-Object System.Data.Odbc.OdbcCommand($sql, $conn);
$rdr = $cmd.ExecuteReader()
$rdr.read()
$rdr.GetValue(0)
$conn.Close();
The below SQL will in a database on a Microsoft SQL-server find all tables with information about the tables schema, name, creation date and modifications date.
select
schema_name(t.schema_id) as schema_name
, t.name as table_name
, t.create_date
, t.modify_date
from sys.tables t
order by table_name
;
The below script in PowerShell can scan a given range of ports for a specific server and will – if a port on the server is open – return the text: TCP port <PORT NUMBER) is open!
foreach ($port in <START PORT>..<END PORT>) {If (($a=Test-NetConnection <SERVER> -Port $port -WarningAction SilentlyContinue).tcpTestSucceeded -eq $true){ "TCP port $port is open!"}}
Example
foreach ($port in 3388..3390) {If (($a=Test-NetConnection MYSERVER -Port $port -WarningAction SilentlyContinue).tcpTestSucceeded -eq $true){ "TCP port $port is open!"}}
TCP port 3389 is open!
NB! I looks like there should be “something” answering in the other end of the port before you will receive a ‘TCP port XXXX is open!‘
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.
An DB2 CLI-driver is a self-sufficient driver that supports a subset of the functions that are provided by the DB2 ODBC-driver.
First download the DB2 ODBC CLI-driver from IBM’s homepage. As such you don’t need to install the driver. You just need to copy it to the location where you want it to reside.
In the below example the directory for the driver is: C:\Software\DB2_ODBC\
The file downloaded from the IBM-homepage is then unpacked into the directory v10.5fp11_nt32_odbc_cli
When the driver is unpacked go to the directory bin. In this directory you need to execute the command db2cli install -setup. This installs/makes the driver visible inside ODBC Data Sources in Windows.
Now you need to create or copy an existing db2cli.ini file to the directory (in this example): C:\Software\DB2_ODBC\v10.5fp11_nt32_odbc_cli\clidriver
Below is an example of what the contents of an db2cli.ini file could be. The db2cli.ini file is “just” a file containing meta-information about the alias that you want to use for you driver inside ODBC Data Sources in Windows.
Now you need to create or copy an existing file db2dsdriver.cfg. This file is to be placed in the directory (in this example): C:\Software\DB2_ODBC\v10.5fp11_nt32_odbc_cli\clidriver\cfg
Below is an example of what the contents of an db2dsdriver.cfg file could be. The db2dsdriver.cfg file contains the more technical information about the alias that you created in the db2cli.ini file. Therefore, the alias in this file must correspond to an alias in the db2cli.ini file. Pointing the alias in the db2cli.ini file to a server and database.
NB! The port number (port=”50000”) in the example below doesn’t have to be the port number used on your system. This is just the default port number for an DB2-instance.
The below blog post will show you how to install make a Microsoft Windows Server a Terminal Server. This was done on a Microsoft Windows Server 2019 Datacenter.
A Terminal Server makes it possible for multiple users to access a Windows Server through Remote Desktop (also called RDP). A normal Windows Server installation is limited to two (2) multiple users.
First off you need to get Terminal Services installed on the Windows Server if this is not already installed.
1.
To do this you need to start Windows PowerShell as an administrator and execute the command below.
Command: Install-WindowsFeature RDS-RD-Server -IncludeManagementTools
2.
Then you need to set the license server under in the Local Group Policy Editor. This is done from the Edit Group Policy located in the Control Panel.
Go to Computer Configuration\Administrative Templates\Windows Components\Remote Desktop Services\Remote Desktop Session Host\Licensing
And choose Use the specified Remote Desktop license servers to set the server.
Here you need to insert the license server provided.
3.
Then you need to set the license mode under in the Local Group Policy Editor. This is done from the Edit Group Policy located in the Control Panel.
Go to Computer Configuration\Administrative Templates\Windows Components\Remote Desktop Services\Remote Desktop Session Host\Licensing
And choose Set the Remote Desktop licensing mode
Here you most likely want to choose Per User
Below SAS-code shows how to make a libname for Oracle directly in SAS-code by providing information about the Oracle server, that you want to access.
libname <YOUR CHOICE> oracle user=<USERNAME> pw=<PASSWORD> Schema=<SCHEMA ON ORACLE>
path="(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = <NAME/IP-ADRESSE OF ORACLE SERVER)(PORT = <PORT FOR ORACLE SERVER. DEFAULT ORACLE PORT IS 1539))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = <DATABASE ON ORACLE SERVER>))
)";
The commands below will scan all *.sas files and look for the text proc. It will of course be possible to use any other string instead of proc. This scenario can be used to scan all your SAS-programs for the usage of SAS-procedures. Be aware that you need to be administrator or run the command as administrator to have access to all folders.
Linux
It will scan from the root of a server and go recursively through all the subfolders it encounters. It will pipe the result of the scan to the file linuxsasproc.txt in the folder where you start the execution of the command – this can of course be changed if you want to.
grep --include=\*.sas -rinw '/' -e 'proc' > linuxsasproc.txt
NB! The –include has two – -.
Output in linuxsasproc.txt. The file is delimited by : (colon)
<Location of SAS-program containing the search string>:<Line number in the file where the search string is found>:<The line that includes the search string>
Example
/sas/sasprogram.sas:24:proc sql;
Addition
In Linux I found that the command find has a maxdepth-option. This option makes it possible to decide, how far down a folder hierarchy a search should be preformed.
For example -maxdepth 1 will not search subfolders. This means that the command below will only search for files (-type f) in all folders matching the folders with wildcard ./sasjobs/runningtime_*/ – but not subfolders of these folders.
find ./sasjobs/runningtime_*/ -maxdepth 1 -type f > result.txt
Windows
It will scan through all subfolder from the location where you start the program. It will pipe the result of the scan to the file winsasproc.txt in the folder where you start the execution of the command – this can of course be changed if you want to.
findstr /s /i proc *.sas > winsasproc.txt
NB! Be aware, that the DOS-commando findstr has an ‘Out of Memory‘ flaw.
Therefore, it can be better to use PowerShell, if this is available for you. The PowerShell command can look something like the below
Get-ChildItem -Path <PATH TO SCAN>:\*.sas -Recurse | Select-String -Pattern 'PROC' | Out-File "<FILE TO CONTAIN OUTPUT>"
An example below
Get-ChildItem -Path C:\*.sas -Recurse | Select-String -Pattern 'PROC' | Out-File "C:\output\sasscanoutput.csv"
Output in winssasproc.txt. The file is delimited by : (colon)
<Location of SAS-program containing the search string>:<The line that includes the search string>
Example
Documents\sasprogram.sas:proc sql;
The command TNSPING can be used to get information about an alias for an Oracle server. Below you can see a screenshot from the command being executed in the command prompt for Windows.
In the command prompt you write: tnsping <the alias of your Oracle server>
TNSPING will return the
| HOST | Showing you the name of the physical host of the Oracle server. |
| PORT | Showing you the port of the Oracle server on the physical host. 1521 is the default port for an Oracle server. |