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
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
Open SAS Management Console
Click on the + next to Schedule Manager. Right click Schedule Manager and click 'Deploy SAS Data Step Program'
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.
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.
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
Open SAS Management Console
Click on the + next to Schedule Manager
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);