Cedric Liem

This is a list of the SAS Dictionary Tables. These tables are accessible using PROC SQL or the corresponding SASHELP library. They are read-only and are useful to get information about libraries, datasets, system options, and other information relating to the current SAS session.

DICTIONARY NAME	SASHELP NAME	DESCRIPTION
CATALOGS	VCATALG	Information on SAS catalogs.
CHECK_CONSTRAINTS	VCHKCON	Information on known check constraints.
COLUMNS	VCOLUMN	Meta info on columns for all tables used in the session.
CONSTRAINT_COLUMN_USAGE	VCNCOLU	Information about columns that are referred to by integrity constraints.
CONSTRAINT_TABLE_USAGE	VCNTABU	Information about tables that have integrity constraints defined on them.
DATAITEMS	VDATAIT	Iinformation about known information map data items.
DESTINATIONS	VDEST	Information about known ODS destinations.
DICTIONARIES	VDCTNRY	Information about all DICTIONARY tables.
ENGINES	VENGINE	Information about SAS engines.
EXTFILES	VEXTFL	Information about known external files.
FILTERS	VFILTER	Information about known information map filters.
FORMATS	VFORMAT VCFORMAT	Information about currently accessible formats and informats.
FUNCTIONS	VFUNC	Information about currently accessible functions.
GOPTIONS	GOPT VALLOPT	Information about currently defined graphics options (SAS/GRAPH software). SASHELP.VALLOPT includes SAS system options as well as graphics options.
INDEXES	VINDEX	Information about known indexes.
INFOMAPS	VINFOMP	Information about known information maps.
LIBNAMES	VLIBNAM	Information about currently defined SAS libraries.
MACROS	VMACRO	Information about currently defined macro variables.
MEMBERS	VMEMBER VSACCES VSCATLG VSLIB VSTABLE VSTABVW VSVIEW	Information about all objects that are in currently defined SAS libraries. SASHELP.VMEMBER contains information for all member types; the other SASHELP views are specific to particular member types (such as tables or views).
OPTIONS	VOPTION VALLOPT	Information about SAS system options. SASHELP.VALLOPT includes graphics options as well as SAS system options.
REFERENTIAL_CONSTRAINTS	VREFCON	Information about referential constraints.
REMEMBER	VREMEMB	Contains information about known remembers.
STYLES	VSTYLE	Information about known ODS styles.
TABLE_CONSTRAINTS	VTABCON	Information about integrity constraints in all known tables.
TABLES	VTABLE	Information about known tables.
TITLES	VTITLE	Information about currently defined titles and footnotes.
VIEWS	VVIEW	Information about known data views.
VIEW_SOURCES	Not available	Contains a list of tables (or other views) referenced by the SQL or DATASTEP view, and a count of the number of references.

In this article, I will show you how to calculate the Month End date, and Fiscal Quarter End Date for Canadian Banks.

Automatically Calculate the Month End Date

This code will calculate the previous month end date automatically based on the date of execution of the code.

data viewdates;
today = today();
date1 = intnx('month',today(),-1,'E'); 
format date1 yymmd7.;
format today yymmdd10.;
run;

proc print data=viewdates;
run;

Result

Assigning the Variable to a Macro Variable

So, the month end calculation is done. But, there is one more thing that needs to be done to utilize this variable in another part of the program. It needs to be assigned to a macro variable that can be used anywhere in the program.

So, the final code looks like this:

data viewdates;
today = today();
date1 = intnx('month',today(),-1,'E'); 
format date1 yymmd7.;
format today yymmdd10.;
call symputx('rpdt',put(date1,yymmddn7.),'G');
call symputx('month',put(date1,monname.),'G');
run;

proc print data=viewdates;
run;

/* These two lines will display the result of rpdt and month in the SAS log */
%put rpdt is: &rpdt;
%put month is: &month;

Result of the %put statements

 68         %put rpdt is: &rpdt;
 rpdt is: 2016-10
 69         %put month is: &month;
 month is: October

This will allow you to use the macro variables, “rpdt” and “month” anywhere else in the program as if it was assigned in SAS as:

%let rpdt = 2016-10;
%let month = October;

Calculate the Fiscal Quarter for Canadian Banks

Surprisingly, in my 10 years of contracting primarily at Canadian Banks, I’ve never seen anyone use this. It’s possible to automatically calculate the fiscal quarter end for a Canadian Bank using this intnx parameter below.

data _null_;
	today = today();
	prev_qtr = intnx('month3.2',today,-1,'E');
run;

What this will calculate is the most recent past fiscal quarter end date for Canadian Banks. So, if “today” is Mar 2, 2017 or any day between Feb. 1, 2017 and Apr 30, 2017, then the date PREV_QTR will be: January 31, 2017.

You can change the ‘E’ to a ‘B’ and then you will get a date of November 1, 2016.