SAS Enterprise Guide

Run code

File -> New -> Program

Highlight code, right click and 'Run Selection on SASApp'

Drop tables (and replace)

To drop tables: %_eg_conditional_dropds(WORK.TABLE_TO_DROP, WORK.TABLE_TO_DROP2);

Drop oracle tables:

PROC SQL;

CONNECT TO ORACLE(USER=&uid ORAPW=&pwd PATH=SERVER.COM);

EXECUTE (TRUNCATE TABLE TABLE_NAME)

BY ORACLE;

DISCONNECT FROM ORACLE;

QUIT;

Then replace table:

PROC APPEND BASE=SCHEMA.TABLE DATA=TABLE;

RUN;

QUIT;

Create a folder for yourself (and give people permission to read it)

To create a 'sand box': LIBNAME UXXXXXX '/sas_projects/XXXXXX';

To give people permissions to you sandbox use a unix statement:

%SYSEXEC cd /sas_projects/XXXXXX/;

%SYSEXEC chmod -710 *;

%put "The return code is &SYSRC";

Concatenate

CATX('',YEAR,MONTH) AS YEAR_MON

Output the first N rows

PROC SQL outobs=10;

CREATE TABLE TMP AS SELECT * FROM TABLE;

Union files

PROC SQL;

CREATE TABLE NEW_TABLE AS SELECT *

FROM TABLE1

OUTER UNION CORR SELECT *

FROM TABLE2

Assign order to table

DATA NEW;

SET OLD;

ID = _N_;

RUN;

Comment text in GUI

Ctrl + / to comment. Crtl + Shift + / to un-comment.

Make all caps

Ctrl + Shift + U

Show variables

%PUT _ALL_;

Get date of last Friday

%LET last_friday2 = %sysfunc(putn(%eval(%sysfunc(intnx(week, "&SYSDATE"D, 0))-2),DATE11.));

%LET last_friday = %sysfunc(prxchange(s/(\S+)/'\1'/,-1,&last_friday2));


%LET last_fridayd3 = %sysfunc(putn(%eval(%sysfunc(intnx(week, "&SYSDATE"D, 0))-2),DATE10.));

%LET last_fridayd2 = %sysfunc(prxchange(s/(\S+)/'\1'/,-1,&last_fridayd3));

%LET track_read_date = %sysfunc(prxchange(s/(\S+)/\1d/,-1,&last_fridayd2));

Convert string to a number

INPUT(ABC_STRING, 9.) AS ABC_NUMBER

Convert number to a string

PUT(ABC_NUMBER, 9.) AS ABC_STRING;

Replace nulls with 0's

DATA TABLE2;

SET TABLE;

ARRAY CHANGE _NUMERIC_;

DO OVER CHANGE;

IF CHANGE=. THEN CHANGE=0;

END;

RUN;

Run SQL code

PROC SQL;

CONNECT TO ORACLE(USER=&uid ORAPW=&pwd PATH=SERVER.HOST.COM);

CREATE TABLE TMP AS SELECT * FROM CONNECTION TO ORACLE (SELECT * FROM TABLE);

DISCONNECT FROM ORACLE;

QUIT;

Truncate a table

PROC SQL;

CONNECT TO ORACLE(USER=&uid ORAPW=&pwd PATH=SERVER.HOST.COM);

EXECUTE (TRUNCATE TABLE TABLE_NAME)

BY ORACLE;

DISCONNECT FROM ORACLE;

QUIT;

PROC APPEND BASE=TABLE_NAME DATA=TMP;

RUN;

QUIT;

Run a scheduled job

  1. Save your file on the SAS server by opening program in SAS and click save as. Click on the Server -> SASApp -> Files -> sas_projects -> XXXXXX

  2. Open SAS Management Console

  3. Click on the + next to Schedule Manager. Right click Schedule Manager and click 'Deploy SAS Data Step Program'

  4. Click on Browse next to Source File. Then mrma... -> sas_projects-> XXXXXX . Click file. Click open. Click ok. Right click Schedule Manager -> Refresh. Do this twice.

  5. Right click Schedule Manager -> New Flow... -> Create a name for the job -> Scheduling Server: 'Platform Process Manager' -> Click on the job -> Click the Right Arrow -> Click ok. Right click Schedule Manager -> Refresh. Do this twice.

  6. Click on the + next to Schedule Manager. Find the job and Right click -> Click 'Schedule Flow' -> Enter username and password -> Click 'Select one or more triggers for this flow' -> Manage -> New Time Event -> Click on the drop down for Calendar Name, then choose 'Daily@Sys'. Next to Hours: choose when you want the event to start (0-23). Next to Minutes: choose when you want the event to start (0-59) For 'Duration of Event', chose how many minutes the script will take to run (maximum) e.g. 360 (6 hours). Click ok -> ok -> ok.

Remove a scheduled job

  1. Open SAS Management Console

  2. Click on the + next to Schedule Manager

  3. Find the job. Right click it. Click delete. Enter username and password. Right click Schedule Manager and click Refresh.

Check schedule log

Servers -> SASApp -> Files -> SAS -> 9.4 -> config -> Lev1 -> Logs. Then find code.

E-mail the job log

%LET log_file='/sas_projects/XXXXXX/Auto_Logs/FILE_NAME.LOG';

PROC PRINTTO LOG=&log_file NEW;

RUN;


%let logclients= "USER@HOST.com" "USER2@HOST.com";

%let mymail= "";

filename mymail EMAIL

to=(&logclients)

from="COMPANY SAS server <HyperionRelay@HOST.com>"

sender=" COMPANY SAS server <HyperionRelay@HOST.com>"

subject="SCRIPT COMPLETED %left(%qsysfunc(date(),worddate18.))"

Attach=&log_file;


data _null_;

file mymail;

put 'SCRIPT ran successfully.';

Send an e-mail from SAS server

%let logclients= "USER@HOST.com" "USER2@HOST.com";

%let mymail= "";

filename mymail EMAIL

to=(&logclients)

from="COMPANY SAS server <HyperionRelay@HOST.com>"

sender=" COMPANY SAS server <HyperionRelay@HOST.com>"

subject="SCRIPT COMPLETED %left(%qsysfunc(date(),worddate18.))";


data _null_;

file mymail;

put 'SCRIPT ran successfully.';

Load data to SAS server

File -> Import Data -> Click on file -> Next -> Tick Rename columns to comply with SAS naming conventions -> Finished.

This gets put into WORK and you can move it into your sandbox.

Or to do it from you laptop

pscp -pw PASSWORD FILE.xlsx USER@HOST.COM:/sas_projects/USER/data/

Upload password to the SAS server

Create a file PW.sas and add:

%LET uid = XXXXXX;

%LET pwd = 'xxx';

%LET uid2 = xxx;

%LET pwd2 = 'xxx';

Remembering to add your username and password,

Save the file on the sas server as File -> Save as -> Server -> SASApp -> Files -> sas_projects -> XXXXXX -> PW -> PWD.sas

See here if you do not have a folder in sas_projects.

Make the file secure by opening a new SAS EG and type:

%SYSEXEC cd /sas_projects/XXXXXX/PW/;

%SYSEXEC chmod 700 *;

In your SAS code add a line at the top as:

%INCLUDE '/sas_projects/XXXXXX/PW/PWD.sas';

E-mail excel table from SAS

PROC EXPORT

data=WORK.TABLE_NAME

dbms= xlsx

outfile="/sas_projects/XXXXXX/data_export/TABLE_NAME.xlsx"

replace;

SHEET = "Sheet 1";

PROC EXPORT

data=WORK.TABLE_NAME2

dbms=xlsx

outfile="/sas_projects/XXXXXX/data_export/TABLE_NAME.xlsx"

replace;

SHEET = "Sheet 2";

run;


%let logclients= "USER@HOST.com" "USER2@HOST.com";

%let mymail= "/sas_projects/XXXXXX/data_export/TABLE_NAME.xlsx";

filename mymail EMAIL

to=(&logclients)

from="COMPANY SAS server <HyperionRelay@HOST.com>"

sender=" COMPANY SAS server <HyperionRelay@HOST.com>"

subject="SCRIPT COMPLETED %left(%qsysfunc(date(),worddate18.))";

Attach= ("/sas_projects/XXXXXX/data_export/TABLE_NAME.xlsx" content_type="application/vnd.ms_excel");


data _null_;

file mymail;

put 'SCRIPT ran successfully.';


data _null_;

fname = "_files";

rc = filename(fname,"/sas_projects/XXXXXX/data_export/TABLE_NAME.xlsx");

if rc = 0 and fexist(fname) then

rc = fdelete(fname);

rc = filename(fname);

run;

Clean up space

%SYSEXEC cd /sups;

%SYSEXEC rm -rf /sups/SAS_util*;

%SYSEXEC rm -rf /sups/SAS_work*;

%xlst(ls -lrt);

%xlog(ls -lrt);