Update your security policies on Windows

If you have ever made a share on a machine running Windows and added security permissions to the share eg giving individual users rights to access the share. You might have noticed that it can take a while before these new security rights is available.

With this command (typed into the command prompt in the operating system) you can force the security rights to be updated immediately:

gpupdate /force

 

Loop a delimited macrovariabel in SAS

This shows how to loop a delimited macrovariabel.

%let Delimitor = ¤;
%let Logins = Login1¤Login2;

%let NumberOfLogins = %sysfunc(countw(&Logins., &Delimitor.));
%put Number of logins: &NumberOfLogins.;

%macro _CreateLogin;

%do J=1 %to &NumberOfLogins;

%let Login = %scan(&Logins., &J, &Delimitor.);
%CreateLogin(_Login=&Login.);

 %end;

%mend;

%_CreateLogin;

Adding path to SASAUTOS

From SAS9.2 it’s possible to add a path to eg SASAUTOS. It’s done using ‘insert’ or ‘append’.
Read more about append and insert.

options insert=(sasautos=&SASProgramPath.\Macros);

If you get a strange error, that insert isn’t recognized or something like that. The error might be caused because there’s spaces in the path, that your trying to insert.

Getting path for the SAS-program being executed

/* Gets path for the SAS-program being executed */

%macro GetSASProgramPath;
%qsubstr(%sysget(SAS_EXECFILEPATH),1,%length(%sysget(SAS_EXECFILEPATH))-%length(%sysget(SAS_EXECFILEname)));
%mend;

%let SASProgramPath = %GetSASProgramPath;

This will only work on Windows.

In batchmode you can get the programname with the code below. This will  work in eg an AIX environment.

%let ProgramName = %sysfunc(getoption(sysin));
%put &ProgramName;

SAS SQL DB2 pass-through eg select

The SAS-code below shows you how to make SQL pass-through to DB2 and collect the result in a new table.

proc sql noprint;

connect to db2 as db(database=<Your database> user=<Your username> password=<Your password>);
create table <Dummy> as
select * from connection to db
(
select * from <schema>.<table>
);
disconnect from db;
quit;

It is also possible to use authdomain to collect credentials instead of user and password and the DB2 database has to be available to SAS.

SAS SQL DB2 pass-through eg index

The SAS code below shows you how to make SQL DB2 pass-through.

proc sql noprint;
connect to db2 (database=<Your database> user=<Your username> password=”<Your password>”);
execute
(

<Your SQL sentence>

) by db2;
disconnect from db2;
quit;

If you have your credentials stored in a SAS metadata-server it is possible to use a authdomain and skip entering username and password.

It is done using the following option in SAS:

options
metaserver=”<Your metadataserver>”
metaport=<usually 8561>
metaprotocol=bridge
metarepository=”Foundation”
;

proc sql noprint;
connect to db2 (database=<Your database> authdomain=<Your authdomain>);
execute
(

<Your SQL sentence>

) by db2;
disconnect from db2;
quit;

The database has to be available to SAS. This can eg be done through your DB2 ControlCenter.

DB2 libname in SAS

The syntax is:

libname <name> db2 database=<database> schema=<schema> user=<user> password=<password> in=<tablespace>;

Eg.

libname testname db2 database=mydb schema=myschema user=domain.com\myuser password=”1234Hello” in=mytablespace;

The connection to the database has to be created eg through the DB2 ControlCenter before it is possible to connect to it in SAS.
‘in=’ determins the tablespace where the data is placed.