Explore topic-wise InterviewSolutions in .

SET and Merge are two very vital statements in SAS. Let US UNDERSTAND by taking 2 tables ‘one’ and ‘two’ and perform SET and Merge Operation on them.

The SET statement is the main tool to read observations from a SAS data set. The basics are so obvious you might not EVEN consider them. Just SET and a data set name and there you go. Through SET statement the dataset gets appended while with merge the two datasets get merged on the basis of ID and extra variables are created if needed. Example below.

OUTPUT of the code

In this CODE, we are trying to show the CHARACTERISTICS of ‘do while’ loop. For each iteration, it’s INCREASING the COUNTER and writing in the output DATASET. Please refer the output table below -

We can clear the libref through one of the below approaches:-

• Using Libname Statement:-

We can use the below syntax to disassociate a specific library

Syntax : LIBNAME libref <CLEAR>

Example : Libname temp_lib clear where temp_lib is the libref that you WANT to deassign If, we would like to disassociate all the libref that we have declared in a SAS    Session, then use the below-modified version

• Using the Libname Function : -

   We can use the LIBNAME function to deassign a library reference. In case, a warning or an error occurs then a message is written to the SAS log.

where temp_lib is the libref that you want to deassign.

Note:- In both the above approaches libref is cleared if and only if there are no open files that are ASSOCIATED from that libref.

• Using SAS Explorer Window:-

On the Explorer window go to libraries. Right click on the library that you want to design and select delete. Once you click on Delete you will receive a Delete confirmation with the text "Are you sure you want to remove the library reference NAMED temp_lib?". Once you click Ok, the same is going to get DELETED.

This code is only an illustration to CREATE a table declares variables name, age, and dob with formats and TAKING values USING datalines.

input name$ age dob date9.; this statement basically creating variables and GIVING format to dob as date9.

Here in this CODE we have A (Amount) = 100000 and I (interest) = 0.0925. We need to find Amount and updated interest till five years also the interest INCREASES by 10% every year.  

In this code, we are giving a data or you can say creating a table USING ‘datalines’ but printing the ROWS which only have status as ‘PT’. We have used LOOP here to CHECK each row and thus printing the desired results.

Output

Because of Cartesian PRODUCT - Cartesian product result-set CONTAINS the number of rows in the FIRST table, MULTIPLIED by the number of rows in the second table.

The MONOTONIC function is quite similar to the INTERNAL variable _N_ in the DATA STEP. We can use it to select the records according to their row number. For example, we choose the SSNs from the 501st LINE to the 888th line in the SSN dataset.

 select * from ssn_data where monotonic() between 501 and 800 ;

you can also the monotonic() to subset the dataset in the where clause to limit the number of records read/outputted to the FINAL dataset…

view source

print?

The INTNX function increments day, year or month specified a date by the parameters given. It is a very helpful function present in sas. Few examples below can SHOW you how - 

 Here it increments the date by 6 weeks, means date after 6 weeks including current week.

output - 

OBS  x  z 

1  7956  31JAN1982 

The intnx function increments the day, year or month on the specified date, you can use the same argument for INCREMENTING 1 month from the date; 

The REVERSE FUNCTION just reverse the STRING, if there are leading BLANKS they become trailing.

Output – 

fedcba

we can count missing value in SAS USING NMISS function.

For example – 

The N function counts the number of non-missing values in a row 

Output – 

we can use CATX function for it. CATX function with a SERIES, Let’s say you want to concatenate the value of a VARIABLE; 

output –

 The mdy function creates a numeric date from the VALUES of the month DAY and year.

Output – 

Let suppose we have a table admit in sasuser library and we have to fetch the student who has age GREATER than the average age then this can be code like the one below using symget.

In SAS, we can convert text into any case like LOWCASE, UPCASE and PROCASE.

LOWCASE – It converts the string into small letters or in lower case.

output – 

UPCASE – It converts the string into CAPITAL letters or in UPPER case.

output – 

PROCASE – It converts the string into proper letters or in other words we can say the FIRST letter upper/upcase and rest in lowercase. Example-

output – 

The tranwrd function helps in the replacement of a string in a char variable. We can reverse the text/values in SAS using tranward function. It has many functions LIKE reversing, REPLACING ETC. 

For example –

output – 

The options nofmterr mprint mlogic symbolgen;

CREATE table work. Account_details as (SELECT Account_ Number, Enroll_ Date, Sales , City  From Master_ acct_ details Where Account_ Number is not null and Sales GT 40000);

Numeric Format AG’ is created, in the work LIBRARY (is a temporary format) with the specified RANGES: 

• 0 to less than 30 coded as ‘young’
• 30 to less than 61 coded as ‘old’
• Format ‘ag’ is CALLED & applied with ‘age’

In case you want to delete multiple delimiters in one GO, you NEED to put all delimiters in the second ARGUMENT in any order and use the SAS function compress. 

Output – 

The mean() function is used to calculate the mean of one observation across the variable. It is used to generate a horizontal DATA summary.

Proc Means is used to calculate the mean of a variable across all observations.

It is used for generating the vertical data summary (i.e. mode of operation is column-wise).

By default, it calculates N, Mean, Std DEV, Minimum, Maximum statistics.

RETAIN statement causes a variable that is defined during the input statement to be retained across data set iterations, in other words, retain statement retains the variable value at each iteration and keeps on substituting it, the pdv does not get cleared when you use the retain statement, LET’s try to understand the basic example explained below –

Table - mylib.clinical

Suppose we have the above clinical table in the form of SAS Dataset in lib – ‘mylib’. CONSIDERING the fact the following code will give the DESIRED results.

retain diag first_date age; /*retaining diag first_date and age at every iteration */

output: 

Dataset of the code – Table: diag_pat

The PAD OPTION can be used to read the source DATA having the variable length records, so to read the variable length of records we can use the PAD option, the PAD option can be used with the infile

statement;

example

Answer

The use of double TRAILING is that, if you have datalines in one line then you can use ;

Output 

The use of SINGLE @ is in the holding the line, it basically is used in more than one input statement, the pointer basically hold the line after reading the values, it basically holds the value and checks the condition;

Output

The SAS processing is in two steps

1. COMPILATION phase  
2. Execution phase

•  Compilation phase
  • If there is a raw data to be read, there is a creation of input buffer a logical concept for holding the data, the input buffer is not created while reading a dataset.
  • After the input buffer, the pdv is created, it is the AREA of memory where SAS builds its dataset.
  • Creating two automatic variables 1. _n_ (takes the value of a number of iterations) and 2. _error_ (takes the value 1 or 0, 1 if errors and 0 if no errors)
  • The SAS then do the syntax checking for the data steps, that includes misspelled keywords, invalid varnames, missing punctuation, and invalid option.
  • Data set variables are created as the input statement is encountered and their length and type are determined.
  • The descriptor portion is now written and this is the FINAL step in the compilation phase, the run statement is the signal for the end of compilation phase. At this point, the data set contain the variables mentioned in the input statement, but the VALUES would only be written in the execution phase.
• Execution phase
  • The automatic variables are initialized _n_ takes the value 1, _error_ to 0 and the remaining variables to missing (.)
  • The input pointer now reads the data values and assigns the variable values to the pdv.
  • At the end of the data step the data values from the pdv are written to the dataset and then _n_ takes the value as 2, _error_ 0 and all rest as missing(.).

Macro is way of automating in sas programs. There are MANY ways to create macro. 

The PAD OPTION can be used to READ the source data having the variable length RECORDS, so to read the variable length of records we can USE the PAD option, the PAD option can be used with the infile

statement;

  example

CONNECTING to teradata using pass through

Other way using the library assignments:

Libname AM DB2 DATABASE= CARD_USA Schema=DDKAUTO

Create table work. Account_details as(Select Account_ Number, Enroll_ Date, Sales, City From AM.Master_ acct_ details Where Account_ Number is not null and Sales gt 40000);

There are multiple ways for creating table in PROC sql

I. Create Table From Column Definition

We can create a NEW table without rows by using the CREATE TABLE statement to define the columns and attributes. We can specify a column’s NAME, type, length, informat, format and label. 

II. Create Table from a Query Result

Many times, there are situations wherein we already have a structure of the table/view and we are trying to create a mirror table that is referring to columns of the earlier existing table. In such cases, we can use the same CREATE TABLE Statement and place it before the SELECT Statement. An additional benefit of using this approach of creating the table is that the data of the new table is derived on the basis of existing data from the parent table.

III. Create Table Like an Existing Table

To create an empty table that has the same columns and attributes as an existing table, we use the LIKE clause while creating the table. One point to note is that an empty table is created using this method and no data is populated inside the table.

createtable employee3 like employee;

quit;

Information From Dataset: A

Information From Dataset: B

The output of the UNION Operator

UNION Operator is used to club the specific variable information from both the tables. Apart from updating the information, it also removes the duplicate data and SORTS the combined information.

The output of UNIONALL Operator

UNIONALL Operator works by appending the information from dataset B after the information from dataset A. It doesn’t remove the duplicates or sorts the resultant data.

Note: If the VARIABLES are EXISTING in both the tables, but the order of the same is different. SAS throws the below error –

• ERROR: Column 1 from the first CONTRIBUTOR of UNION is not the same TYPE as its counterpart from the second.
• ERROR: Column 2 from the first contributor of UNION is not the same type as its counterpart from the second.

Format for creating a simple index -

Note: We can also use the proc’s to create the indexes in the newly generated output DATASETS.

Additionally, we COULD also use the proc datasets procedure and proc SQL to generate indexes.

SUPPOSE we have the above clinical table in the form of SAS Dataset in lib – ‘mylib’. Considering the fact, the following code will give the desired results. 

output: 

Dataset of the code – Table: diag_pat