How to give your SAS-session a name

The DOS batch code below let’s you give your SAS-session a name – instead of just showing “SAS” on the procesline. The code gives you the possibility to differentiate multiple SAS-sessions from each other on the process line.
You just make a .bat-file containing the code below. Then you drag and drop the shortcut for your SAS-session onto the .bat-file and the .bat-file will start by asking you to give the SAS-session a name. When you have entered a name and pressed the Enter-key, it will start a SAS-session showing the name you just entered.

You will have to drag and drop your SAS-shourtcut on to the .bat-file containing the code above.
pic20903

 

 

 

 

 

 

The .bat-file will ask you to give the SAS-session a name.
pic23687

Now you will have open SAS-session on the process line in Windows with the name you have just given them.
pic24521

Getting filename from SYSIN-option in SAS

The code below makes it possible to extract the filename from the filename being executed through the SYSIN-option in SAS.

 

Include macros in SAS

This macro will include the external macros in a library on the disk. This will be done, so you are sure that SAS will choose the external macros not the same macros in libraries included in SASAUTOS. The macros first choosen by SAS will be the macros found in Work.Sasmacr library. Including the external macros will put these macros in this library. Example : %IncludeExternalMacros(dir=&RootPath/Macros/External/);

It’s also possible to use this simple piece of SAS-code found below.

 

 

Loop through datasets in SAS

This code was found here. It lets you loop through datasets in a SAS-library and tries to print the first 10 observations from each dataset in the SAS-library.

 

Call Execute lets you run PROC-statements in a datastep. It will simply execute the proc-statement after finishing the datastep. It is also possible to use ‘if‘, ‘then‘ and ‘else‘ in the datastep to execute certain parts of the code based on some value.
This is of course also possible through macro-code.

Writing a SAS-program in SAS

The code below will let you write a SAS-program in SAS.

/* This dummy macro-variable will be used later as input for the SAS program. */
%let HelloWorld = Hello world;
data _null_;
file SASProgram;
put ‘%let Hello='” &HelloWorld. “‘;’;
put ‘%put &Hello;’;
put ‘run;’;
run;

filename cdrive “C:\SASPrograms”;

data _null_;
infile SASProgram;
file cdrive(HelloWorld.sas);
input;
put _infile_;
run;
filename cdrive clear;

You now have a SAS-program called ‘HelloWorld.sas‘ in the folder ‘C:\SASPrograms\‘.
The program will look like this.

%let Hello= Hello World;
%put &Hello;
run;

And it will write ‘Hello World‘ to the log in SAS.

Signon to remote SAS

Below is an example of how you can signon to a remote SAS-server and execute SAS-commands.

/* Insert the name of the server that you want to connect to and the number of the port. It is usually 7551. The portnumber is the where your local SAS for the SAS/CONNECT service on the remote machine. */
%let remote=<Your server> 7551;
/* Gets your userid from the system and prompts you for your password when connecting. */
options comamid=tcp remote=remote; signon remote user=&sysuserid passwd=_prompt_;

/* Makes a libname for your workdirectory on the remote server. */
libname remtwork slibref=work server=remote;

/* The macro below is executed on the remote server and creates a libname for a userfolder on the remote server. The userfolder hos to have the same name as the macro &sysuserid. */
rsubmit;
%macro UserLib;
%let user = %sysfunc(substr(&sysuserid,4));
libname &user.Lib “f:\users\&sysuserid”;
options compress=binary;
%mend;
%UserLib;
endrsubmit;

/* This creates a local libname that points to the user libname on the remote server. */
%let user = %sysfunc(substr(&sysuserid,4));
libname &user.Lib slibref=&user.Lib server=remote;

SAS and SVN

Below is shown how you can extract the current SVN revision number for a given path into SAS and use it in your SAS program.

You can now use the three diffent macrovariables called svnver1, svnver2 and svnver3 in your program.

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

Connecting to Azure SQL-server from SAS

This post shows how it is possible to connect to an SQL-server server/database (PaaS) in Windows Azure from SAS.

First of all you need to create a SQL-server database. When you create an SQL-server database it will also create a virtual server.The servername for the virtual server is the “funny” name eg. aoe8kg2q9w.
It is on this server, that you need to open for the different IP that need access to the SQL-server.

As shown below you go to the Servers-tab and click on the server where you want to change the firewall settings.

AzureServers

On the server you go to the Configure-tab and enter the IP-adress that has to access the SQL-server.

IPAccess

It is possible to get different connection strings for your SQL-database on Azure. As shown below you need to choose ‘View SQL Database connection strings’.

ConnStrings

Here you can eg find the connection string for an ODBC connection and use it in your libname statement for SAS.
Now you can connect through SAS. First you make a libname. This has only worked for me when I use one of the new ODBC-drivers for Microsoft SQL-server eg. ODBC Driver 11 for SQL Server.

libname azure odbc NOPROMPT=”Driver=ODBC Driver 11 for SQL Server;Server=tcp:<SERVERNAME>.database.windows.net,1433;Database=<DATABASE>;Uid=<USERNAME>@<SERVERNAME>;
Pwd=<PASSWORD>;Encrypt=yes;Connection Timeout=30;” schema=<SCHEMA>;

Below I have made an example. Where I copy sashelp.class to my Azure database.
I need to create an identical copy of sashelp.class on Azure.

proc sql;
create table azure.class like sashelp.class;
quit;

I need this copy because Azure needs a clustered index on the table. If you don’t have a clustered index on the table, it is not possible to load data into the table.

proc sql noprint;
connect to odbc  (NOPROMPT=”Driver=ODBC Driver 11 for SQL Server;Server=tcp:<SERVERNAME>.database.windows.net,1433;Database=<DATABASE>;Uid=<USERNAME>@<SERVERNAME>;
Pwd=<PASSWORD>;Encrypt=yes;Connection Timeout=30;”);

execute  (
CREATE CLUSTERED INDEX idxName ON [SCHEMA].[class]   (    [Name] ASC   )WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]  ) by odbc;
disconnect from odbc;
quit;

Now that I have made the clustered index on the table. It is possible to load data into the table.

proc sql;
insert into azure.class
select * from sashelp.class;
quit;