Data

Page tree



Back to Knowledge Base

List of SAS Viya Global Macros

The global macros provided below have been set up to enable a user to get quick information from Viya to schema/tables in Hive.  



SAS Viya Global Macros

Click the appropriate tab to display the macro's name, its purpose, and an example or note about the macro.


Macro%help()
PurposeThe help macro shows documentation links and provide information on available system macros.
Example/NoteNA
Macro

%send_email(to_email, subject, body, attachment);

PurposeSends an email from SAS Viya to one or more recipients with an optional attachment.
Example/Note

%send_email() was updated, and users can no longer specify the sender.

from_email parameter is now hardcoded to comply with HCQIS guidelines that all email must sent from a hcqis.org email address.

Sender will now always be: ccsq-sas-viya@hcqis.org

Important Note: Remove the from_email parameter from the macro calls.

%send_email(to_email="me@me.com", 
subject="Process - STARTED", body="Process started @ %SYSFUNC(DATE(),MMDDYY.) %SYSFUNC(TIME(),hhmm.)");

%send_email(to_email="me@me.com coworker@me.com",
subject="Process - ENDED", body="Process ended @ %SYSFUNC(DATE(),MMDDYY.) %SYSFUNC(TIME(),hhmm.)",
attachment="/workspace/workbench/myspace/data/logoutput.txt");


Update

%send_email( ); has been updated to send email as HTML and the "Last error" line has been removed.

A new parameter - send_html defaults to "Y" to send HTML email: 

%send_email(to_email="(your email address)",

subject="HTML Test", body="<font color=red>Red text</font>");


If you want to send email as plain text set send_html="N" 

%send_email(to_email="(your email address)",

subject="Plain - text", body="Plain text", send_html="N");

Warning

Make sure the logs do not include protected health information (PHI)/personally identifiable information (PII)!

Macro

%list_databases()

PurposeLists the databases that are visible to users.
Example/Note

NA

Macro

%list_caslibs()

PurposeLists the CASLIBs that are visible to users.
Example/Note

NA

Macro

%list_tables(schema)

PurposeLists the tables in a database.
Example/Note

%list_tables("nch_part_a");

Macro

%list_columns(schema, table)

PurposeLists the columns in a table.
Example/Note

%list_columns(schema="nch_part_a", table="hha_line");

Macro

%list_partitions(schema, table)

PurposeLists the partitions in a table.
Example/Note
%list_partitions(csat_201908_baseline, csat_abase_201908_ak);

Note: Use partition for filtering in addition to any filter that might already be in use By doing so, it dramatically cuts down on processing time and system resources needed.
Macro

%show_rowcount(schema, table)

PurposeLists row count and size of a table.
Example/Note

%show_rowcount(csat_201908_baseline, csat_abase_201908_ak);

Macro

%hive_exec_sql(sql, queue=[unspecified]|heavy);

Purpose

Executes SQL code directly in Hive - recommended over PROC SQL in SAS since it is faster than an implicit PROC SQL in SAS.

queue: [unspecified] - use default queue | heavy - use the queue for larger processing tasks.

Example/Note
%LET sql=%STR(CREATE TABLE &myschema.my_exec_test AS SELECT * FROM claims_sample LIMIT 100);
%hive_exec_sql(&sql);
%LET sql=%STR(CREATE TABLE &myschema.my_exec_test AS SELECT * FROM claims_sample LIMIT 100);
%hive_exec_sql(&sql, queue=heavy);
Macro

%hive_drop_table(schema,table)

PurposeDrops the schema.table indicated in Hive.
Example/Note

NA

Macro

%usage_hive(schema, [show_tables=Y]);

PurposeShows the amount of space used by a user's project database and optionally details on each individual table.
Example/Note

%usage_hive(); * Show usage for all project databases a particular user has access to;

%usage_hive(schema="damod"); * Show the total usage for the damod schema [insert user's own schema name];

%usage_hive(schema="damod", show_tables=Y); * Show usage for each table in the damod schema [update for a user's schema];

Macro

%gzip(source_file);

PurposeCompresses a single file with gzip compression and outputs to the same folder.
Example/Note

%gzip(source_file=[full path to source file]);

Macro

%gunzip(gzip_file);

PurposeUncompress a single gzipped file to the same folder.
Example/Note

%gunzip(gzip_file=[full path to .gz file]);

Macro

%hive_explain(sql);

PurposeShows the Hive execution plan for the SQL query passed.
Example/Note

%LET sql=%STR(CREATE TABLE &myschema.show_the_plan AS SELECT * FROM claims_sample.hha_header LIMIT 100);
%hive_explain(&sql);

Macro

%queue_status();

Purpose

Shows users information about the Hive queue.

Three sections:

  • Queue summary
  • Active queries
  • Most recently completed queries
Example/Note

Note:

Active queries only display when users have used %hive_exec_sql() or PROC SQL – CASLIB loads operate differently and will not appear. 

Macro

%zip();

Purpose%zip();Compresses a folder with zip compression. There is no deletion of any files in zipping, the macro creates copies which are then zipped.
Example/Note

%zip(zip_file=file_name_of_zip, source_folder=full_path_to_zip);


Note

  1. The zip code only operates on folders. If users are trying to zip a file in a folder, they will need to specify the parent folder and then that folder will be zipped. Alternatively, if a user would like to zip a single file they can use %gzip(); - explanation on this macro is found on Section 12 of this list.
  2. Users that are going to zip, must zip the subfolder, not the main folder. 
Macro

%ps();

Purpose%ps(); will show a list of processes that a user is running - both the interactive window and any background or scheduled tasks.
Example/Note


User Processes list


Note

  1. * will show for the current interactive process .
  2. The Process list is updated every few minutes. 
Macro

%queue_status();

Purpose%queue_status(); will show the process identification (PID) of the SAS process running a Hive query.
Example/Note

Queue status list


Note

The PID will only show if a user is using %hive_exec_sql() - if they are using PROC SQL, which is discouraged, then they WILL NOT see their PID in this list because PROC SQL cannot pass that information to Hive.

Macro

%kill();

Purpose%kill(); will allow users to kill their own process. 
Example/Note

%kill(PID);

Important

Users can only kill their own process - if they submit a process identification (PID) that is for another user or that is invalid, the request will be ignored.

Kill Your Process window

Note

It will take a few minutes for the process to be killed. 

Macro

%job_start( );

%job_status( );

%job_stop( );

Purpose

To improve the ability to manage long-running or background tasks, three more macros have been added:

%job_start( ); - will start writing log file to current workbench folder. 

%job_starus( ); - will write an update to the log file and optionally send an email update with a copy of the log file so far.

%job_stop( ); - will end logging and send an email with the completed log file. 

Example/Note


Important

Make sure the logs do not include protected health information (PHI)/personally identifiable information (PII)!

An example of batch processing/logging:

1) Set the log levels (igh, normal, none): 

%log_level(high);

2) Required: Set the email address: 

%LET BATCH_USER_EMAIL=youremail@adress.com;

3) Start batch processing code: 

%job_start();

4) Run the code (note: no PHI in the following code):

DATA work.cars;
SET sashelp.car;
RUN;

5) Stop batch processing: 

%job_stop();
Macro

%search_cdr( );

Purpose%search_cdr( );  will allow users to search the Centralized Data Repository (CDR) database/schema names, table names, and column names.
Example/Note

Basic Search

  • %search_cdr(beneficiary);  this will search all options.

CDR Search Results

  • %search_cdr(schema=nch); to search schema names.
  • %search_cdr(table=carr); to search table names. 
  • %search_cdr(column=carr); to search column names.


Composite Search 

  • %search_cdr(schema=nch, table=carr); to search for multiple things at once. 

CDR Search Results

Note

There is a limit of 100 results and upon first use during the session some data is cached to work.cdr_info for faster subsequent searches – this dataset can be ignored. 

Macro

%usage_workbench( );

Purpose%usage_workbench( ); will allow users to view the amount of space they are using in their workbench.
Example/Note

Example usage: 

  • %usage_workbench([workbench_folder_path]);
  • %usage_workbench(damod);
  • %usage_workbench(damod/data/pgrivas);

Workbench S3 Usage for damod