SYS-ED SAS Training Services SYS-ED Experience

Submit SAS Questions


Submit SAS Questions

Technology Driven IT Training

Knowledge Base

Submit SAS Questions to SYS-ED Advanced search

Interrelated Information Technology

Database Questions Programming Language Questions

The SYS-ED knowledge base is a service for answering questions, inclusive of the research and validation of the accuracy of information in the public domain. Citation of source documentation and examples are used to provide answers to the questions. Utilization and reliance on the answers, information, or other materials received through this website is done at your own risk.


My company just upgraded to SAS 9; what new macro features are available?


The new macro features include:

MLOGICNEST/MPRINTNEST Indicates whether a macro has been invoked from within another macro.
MAUTOLOCDISPLAY Will display the directory path and filename of the invoked autocall macro.
MCOMPILENOTE Writes a NOTE in the log indicating that the macro has compiled successfully. The default value of this option is NONE.
%ABORT Stops the macro that is executing along with the current SAS job, or SAS session.
%RETURN Causes normal termination of the currently executing macro.
CALL SYMPUTX Assigns a value to a macro variable and removes both leading and trailing blanks.
&SYSNCPU Contains the current number of CPUs that SAS can use during the current SAS session.


Is it possible to remove macro code from my program in order to make debugging the other SAS statements easier?


The MFILE and MPRINT system options can be used for removing macro code by using the MFILE and MPRINT system options. The MFILE option routes the code generated by the macro (the MPRINT output) to a specified file.


I'm trying to use SAS/ACCESS to use DB2 data for the first time and I get the following error: ERROR: Cannot connect to DB2 subsystem, rc = 00F30034. What's wrong?

A The SAS/ACCESS interface to DB2 is shipped with one application PLAN (SASDB2E) that the installer binds to the DB2 subsystem(s) at installation time. The error probably means that the DB2 Database Administrator has not yet granted you EXECUTE privilege to the plan.

Q How do I export my SAS dataset to a specific sheet name in Excel?
A Beginning with Release 8.1 of SAS/ACCESS to PC File Formats, the sheet name in the specified workbook will have the same name as the SAS dataset.


I have been having problems with the page number and date at the top of my output. Can this problem be corrected?

A SYS-EDís SAS Programming Essentials course will teach how to resolve this problem, along with several other undocumented tips. FYI, NONUMBER and NODATE system options will remove the page number and date.


I am new to SAS programming and I have two questions:

  1. I need to be able to hide my code during program execution in order that it does not appear in the log.
  2. How can I get rid of duplicates in a SAS dataset?
A The answer to the first question is that the code can be hidden by storing the code as a compiled macro. Compiled code will not be seen in an editor and options that write information about the code to the log can be turned off in the macro. Only warnings and errors will be written to the log.

The answer to the second question is that duplicates can be suppressed by using PROC SORT with the NODUP or NODUPKEY option specified.

An alternative way of achieving this would be to use a DATA step with the added benefit that the DATA step will allow the duplicates to be output to another file.

Q Is it possible to create an output dataset of a specific table or send to output a particular statistic that is displayed by a procedure?
A The ODS: Output Delivery System OUTPUT statement is a global statement which can be used for sending to output any table that is created by any procedure. However, this statement is only available with release 7 or higher versions of SAS.

Q I am new to SAS and I have been given the responsibility for managing date values in an external file that contain four-digit years. I need to reformat the values as SAS date values.
A This is done by selecting the SAS informat that corresponds to the format of the required date and then increasing the width specification to accommodate the four-digit year.

Q I am familiar with managing comma or tab delimited files in Excel and Access, how does SAS read a comma- or tab-delimited file?
A The DLM= option in the INFILE statement is used to specify that the file is delimited. In order to have multiple consecutive delimiters treated as missing values, the DSD option can be added. When there are values represented by a null value then the DLM=option on the INFILE statement will need to be used.

Q How can I merge a single observation with all observations in a SAS dataset?
A A merge can be performed by executing the SET statement to read the single observation dataset on the first iteration of the DATA step only.

Q How can I get the DB2 return code and message from a failed DB2 SQL statement in PROC SQL's Pass-through facility?
A The SQL return code is saved in the macro variable &SQLXRC and the corresponding DB2 error message from the submitted SQL statement is saved in &SQLXMSG. The SQL return code only can be returned from a failed SAS/ACCESSS view via the macro variable &SYSDB2RC.