/*==========================================================================
* SAS BASE CODE TIP 01 OPTIONS & LIMIT SIZE DATASET
*==========================================================================*/
OPTIONS NOTES SOURCE SOURCE2 SYMBOLGEN MPRINT MLOGIC OBS=1024;
PROC SQL OUTOBS=1024;
SELECT
*
FROM SOMETHING_LARGE
;
QUIT;
OPTIONS NONOTES NOSOURCE NOSOURCE2 NOSYMBOLGEN NOMPRINT NOMLOGIC OBS=MAX;
/*==========================================================================
* SAS BASE CODE TIP 02a DETECT LONG TABLE - & COLUMN NAMES
*==========================================================================*/
%LET dbLibname=SOMELIBY;
PROC SQL;
CONNECT USING &dbLibname. AS Database
;
CREATE TABLE &dbLibname._TableNames AS
SELECT
*
FROM CONNECTION TO Database
(
SELECT
*
FROM information_schema.tables
)
;
CREATE TABLE &dbLibname._TableColumnNames AS
SELECT
*
FROM CONNECTION TO Database
(
SELECT
*
FROM information_schema.columns
)
;
DISCONNECT FROM Database
;
SELECT
"&dbLibname." AS library,
table_catalog,
table_schema,
SUM(LENGTH(table_name) > 32) AS tables_name32plus FORMAT=COMMAX16.,
COUNT(*) AS tables FORMAT=COMMAX16.
FROM &dbLibname._TableNames
GROUP BY
table_catalog,
table_schema
;
SELECT
"&dbLibname." AS library,
table_catalog,
table_schema,
COUNT(DISTINCT table_name) AS tables FORMAT=COMMAX16.,
SUM(LENGTH(column_name) > 32) AS columns_name32plus FORMAT=COMMAX16.,
COUNT(*) AS columns FORMAT=COMMAX16.
FROM &dbLibname._TableColumnNames
GROUP BY
table_catalog,
table_schema
;
SELECT
"&dbLibname." AS library,
table_catalog,
table_schema,
table_name AS table_name32plus LABEL=''
FROM &dbLibname._TableNames
WHERE LENGTH(table_name) > 32
ORDER BY
table_catalog,
table_schema,
table_name32plus
;
SELECT
"&dbLibname." AS library,
table_catalog,
table_schema,
table_name,
column_name AS column_name32plus LABEL=''
FROM &dbLibname._TableColumnNames
WHERE LENGTH(column_name) > 32
ORDER BY
table_catalog,
table_schema,
table_name,
column_name32plus
;
QUIT;
/*==========================================================================
* SAS BASE CODE TIP 02b ADDRESS SAS INCOMPATIBLE TABLE NAME
*==========================================================================*/
%LET dbLibname=SOMELIBY;
PROC SQL;
CONNECT USING &dbLibname. AS Database
;
CREATE TABLE WORK.ShortTableName AS
SELECT
*
FROM CONNECTION TO Database
(
SELECT
*
FROM dbo.ThisIsVeryDescriptiveButLongerTableNameThanNecessary
)
;
DISCONNECT FROM Database
;
QUIT;
/*==========================================================================
* SAS BASE CODE TIP 03 WORK LEAN & COMPRESS
*==========================================================================*/
PROC SQL;
CREATE TABLE WORK.TESTDATA (COMPRESS=YES) AS
SELECT
Columns,
YouActually,
Use
FROM SQL_DM.SALES
;
QUIT;
DATA WORK.TESTDATA (COMPRESS=YES KEEP = Columns YouActually Use);
SET SQL_DM.SALES;
RUN;
/*==========================================================================
* SAS BASE CODE TIP 04 CHECK YOUR RESOURCE FOOTPRINT
*==========================================================================*/
%LET lib=WORK;
PROC SQL;
SELECT
libname AS Library LABEL='',
COUNT(DISTINCT memname) AS Tables,
SUM(nvar) AS Variables FORMAT=COMMAX16.,
SUM(nobs) AS Observations FORMAT=COMMAX16.,
SUM(obslen) AS Length FORMAT=COMMAX16.,
SUM(filesize / (1024*1024)) AS TablesSize_MB FORMAT=COMMAX16.2,
SUM(pcompress > 0) AS TablesCompressed FORMAT=COMMAX16.,
COUNT(*) AS OBS FORMAT=COMMAX16.
FROM dictionary.tables
WHERE libname IN("&lib.")
GROUP BY
libname
;
SELECT
MONOTONIC() AS nr,
libname AS Library LABEL='',
memname AS TableName LABEL='',
nvar AS Variables FORMAT=COMMAX16. LABEL='',
nobs AS Observations FORMAT=COMMAX16. LABEL='',
obslen AS Length FORMAT=COMMAX16. LABEL='',
filesize / (1024*1024) AS TableSize_MB FORMAT=COMMAX16.2 LABEL='',
pcompress / 100 AS Compression FORMAT=PERCENTN16.1 LABEL='',
encoding AS Encoding LABEL=''
FROM dictionary.tables
WHERE libname IN("&lib.")
ORDER BY
TableSize_MB DESC,
TableName
;
QUIT;
/*==========================================================================
* SAS BASE CODE TIP 05 THE LITTLE SAS BOOK
*==========================================================================*/
https://www.amazon.nl/-/en/Lora-D-Delwiche/dp/1642952834
/*==========================================================================
* DONE
*==========================================================================*/