Shriram Dahotre

USAS SAS Function senten sent. at MCR

• Pakistan
• April 1986 to March 2017

SAS tips foe week: Exploring Dictionary Tables in SAS
Definition
The SAS System generated and maintains valuables information at run time about SAS libraries, data sets, catalogs, indexes, macros, system options, titles, and views in a collection of read-only tables called dictionary tables Although called tables. Dictionary tables are not real tables. Information is automatically generated at runtime and each tables contents are made available once a SAS session’s is started.
Dictionary tables and SAS HELP views contents permit a SAS session’s- activities to be accessed and monitored. This become particularly useful in the design and construction of software applications since the information can be queried and the results acted upon in a specific task such as in the allocation of filerefs or librefs.
Dictionary table Utility Queries
1) to display a table definition
You will be able to see the table DDL in the SAS Log
PROC SQL;
DESCRIBE TABLE DICTIONARY.OPTIONS;
QUIT;
2) To display Unique dictionary tables in the dictionary
PROC SQL;
SELECT UNIQUE MEMNAME
FROM DICTIONARY.DICTIONARIES;
QUIT;
3) Retrieving information about the columns in one or more data sets or tables.
You will able to capture column-level information including columns name, type, length, position, and indexes, position, label, format, information and indexes, as well as produce cross reference listings containing the location of columns in a SAS library
PROC SQL;
SELECT *
FROM DICTIONARY.COLUMNS
WHERE UPCASE(LIBNAME)=”DA_TEAM” AND UPCASE(NAME)=”MDS_CNTRL_VLDN_CE”;
QUIT;
4) Retrieving information about the tables in one or more library
This providers detailed information about the library name, member name and type, create created and last modified, number of observations, observation length, number of variables, password protection, compression, encryption, number of deleted observations, type of indexes, and requirements vector.
PROC SQL;
SELECT *
FROM DICTIONARY.TABLES
WHERE UPCASE(LIBNAME)=”DA_TEMP”;
QUIT;
For more information visit
http://support.sas.com
happy Learning
Handling Duplicates in SAS (Method-1)
Duplicates using Proc Sort Procedures:
There are two methods of removing duplicate observations in a PROC SORT. we can use the proc sort procedures option NODUPKEY or NODUPREC
NODUPKEY OPTIONS
“Creaks for and eliminates observations with duplicates BY values. If you specify this option, then the PROC SORT compares all BY values for each observation to those for the previous observation that is written to the output data set. if an exact match is found, then the observation that is written to the output data set.”
NODUPRECS (OR NODUP) OPTION:
“Creates for and eliminates duplicate observations. if you specify this option, then PROC SORT compares all variable values for each observation to those for these for the previous observation that was written to the output data set. if an exact match if found, then the observation is not written to the output data set.”
DIFFERENCE BETWEEN NODURKEY AND NODUPRECS
NODUPKEY checks observations based on the “key” variables: the list of variables in the BY statement. NODUPRECS checks all variables, not just “key” variables.
DUPOUT=option
This enables users to send the duplicate observations to a data set specified in the DUPOUT=option
Sample Codes:
The below code will delete duplicates at by variable SBSCRBR_ID MBR_SQNC_NBR YEAR_MNTH_NBR level and the DUPOUT option will send the duplicates to a new dataset so that you come to which all observation are duplicates and got delete from the original data set.
PROC SORT DATA

• to

first sort the data set by the key variable or variables. the key variable define unique observations
proc sort data=SBSCRBR out=SBSCRBR1;
by SBSCRBR_ID;
run;
a FIRST.VAraible and LAST.Varaible are created for each variables listed in BY statement of a DATA step and take on the values 1 to 0. FIRST.SBSCRBR_ID is assigned the value of 1 for the first observation in a BY group and the value of 0 for all other observations in the BY group. LAST.SBSCRBR_ID is assigned the value of 1 for the last observation in a BY group and the value of 0 for all other observations in the BY group. displaying the first.Variable and last.Varaible
data test;
set SBSCRBR1;

at JEFFERSON, THOMAS”; DOB

• to

; OUTPUT;

PRESIDENT at FRANKLIN

• United Arab Emirates
• to

; OUTPUT;
RUN;
Name
Dob_char
Prez

at WASHIONGON, GEORGE”; DOB

• to

For more information refer SAS online documentation
Happy Learning
SAS* SUBSTR Function
The SAS* SUBSTR Function differs from the substring function in other programming languages as it can used on either side of the assignment operators.
Syntax:
The syntax for the SUBSTR function of the left-hand side of the equal sign is.
SUBSTR (String, starting position ) =’characters-to-replace’;
Where
*string refers to a variable containing the string or to the literal string.
*the starting position can be determined by counting the string characters from to right. a starting position of one identifies the first character of the string.
3) Length is an optional number of characters to extract. The default value of length value of length operators on the remainder of the string. for this reason, length can also be thought of as the end positions of the sting.
Note: SUBSTR is often used in compunction with the index function to determine starting positions and lengths.
Uses of SUBSTR on the left-hand side
*to allow comparison of values based on the prefix of a text string.
2) to populate a string at a specified position or columns
Sample code:
DATA CITIZENS;
LENGTH NAME $20 DOB_CHAR $9 Prez $4;

at ID

• Qatar
• to

24 100 97 0 100 85 85
28 0 87 98 100 0 90
60 100 0 0 100 100 100
65 100 98 100 0 90 100
70 99 97 100 100 95 100
40 97 99 98 0 100 95
190 100 0 97 100 100 90
196 100 100 0 100 100

at input ID

• to

Keep would pick up only there three fields in input datasets */
run;
/*in this example Drop statement appliers to all the output datasets */
data work.temp1 temp2;/*output datasets with all the fields except age*/
set sashelp.class(keep=name sex age);
drop age;
run;
3) the RENAME data set option allows you to specify the variables you want to renames for each input or output data set.
-RENAME=option in the output dataset will change name of variables appearing in corresponding output dataset.
-RENAME statement will change the name of variable in all the output dataset.
-Ex
data dataset1 dataset2;
rename age=new_age old_var=new_var;/*Statement-changes applied to all output dataset */
set dataset2(rename(temp1=temp2));/*For Processing one has to use temp2 variable name.temp1 will no longer be available */
run;
data dataset1 dataset2(rename(age=new_age old_var temp1=temp2));
/*option-variables will be renamed only in dataset2 and non in dataset1*/
set dataset2;
run;
-The RENAME=data set options in the SET statement renames variables in the input data set before processing. One can use the new names in programming statements for the current DATA step.
Points to ponder:
-DROP and KEEP options can’t be used together.
-KEEP and RETAIN statement. The RETAIN statement causes SAS to hold the value of a variables from one iteration. The KEEP statement does not affect the value of variables but only specified which variables to include in any output data set.
-Input datasets get affected only because of options whereas output datasets can be modified by options as well as statements.
-Drop the variable from input dataset and would no longer be available for processing
-Proc step can have only options. data step allows statements as well as options.
SAS arrays and Missing Values
Change all missing values of all variables into zeros / putting zeros in place of missing values for variables.
You always wondered how do you convert missing values for and the variables into zero and in the below example the array is used to do the same.
Convert all missing Values
The variables list includes ID and Score1 to score6. Using simple array methods we can change all the missing values for the variables score1 to score6 to 0.
data missing;

at ID

• Qatar
• to

24 100 97 1 100 85 85
28 1 87 98 100 1 90
60 100 1 1 100 100 100
65 100 98 100 1 90 100
70 99 97 100 100 95 100
40 97 99 98 1 100 95
190 100 0 97 100 100 90
196 100 100 0 100 100

at form

• to

.;
vardt = “03MAR12”d

at form

• to

.;
vardt = “07MAR12”d;

• to

The following program will randomly extract the sample
data sample;
set sashelp.class;
if ranuni (12345)