This is the second part of the SAS Base Programming Exam Guide to prepare for the Base Programming Specialist Exam.

Sort observations in a SAS data set

Use the SORT Procedure to re-order observations in place or output to a new dataset

Here, we will sort the sashelp.shoes dataset and here is a partial output of the unsorted dataset:

So, we will use the sort procedure to create a new dataset, work.shoes that will sort the sashelp.shoes. We will sort based on Sales column in descending order and Inventory column in ascending order in that order.


/* Sort sashelp.shoes dataset by Sales in descending order and 
   then Inventory in ascending order and output results to work.shoes
*/
proc sort data=sashelp.shoes out=work.shoes;
	by descending Sales Inventory;
run;

/* Print out the sorted results */
proc print data=work.shoes;
run;

Partial sorted output:

Conditionally execute SAS statements

Use IF-THEN/ELSE statements to process data conditionally


data work.condition;
	/* Produce two variables with y's value conditional on the value of x */
	x = 5;
	if x = 5 then y = 'Hello';
	else y = 'World';
run;

proc print data=work.condition;
run;

Output:

Use DO and END statements to execute multiple statements conditionally


data work.loop;
	/* Produce three variables with x, y, z values conditional on the value of a */
	a = 1;
	if a = 1 then do;
		x = 'A';
		y = 'Is';
		z = 'Not Zero';
	end;
	else do;
		x = 'A';
		y = 'Is';
		z = 'Zero';
	end;
run;

proc print data=work.loop;
run;

Output:

Use assignment statements in the DATA step

Create new variables and assign a value

Assign a new value to an existing variable

Assign the value of an expression to a variable

Assign a constant date value to a variable


data work.variables;
	/* Create a new variable and assign a value to it */
	var = 'Value';
	
	/* Assign a value to an existing variable */
	existing_var = var;
	
	/* Assign the value of an expression to a variable */
	math_var = 5 + 6;
	
	/* Assign a data expression to a variable */
	base_date = '01Jan1960'd;
run;

proc print data=work.variables;	
run;

Output:

Modify variable attributes using options and statements in the DATA step.

Change the names of variables by using the RENAME= data set option.

Use LABEL and FORMAT statements to modify attributes in a DATA step.

Define the length of a variable using the LENGTH statement.

sashelp.class dataset
sashelp.class dataset

data work.class;
	/* Define the length of a variable using the LENGTH statement */
	length Fname $3; * Let the First Names have at most 3 letters;

	/* Change the names of variables by using the RENAME= data set option */
	set sashelp.class (RENAME=(Name=Fname));

	/* Use LABEL and FORMAT statements to modify attributes in a DATA step */
	label Sex = 'Gender'; * Label Sex variable with the value 'Gender';
	format Weight 3.; * Format Weight variable to have no decimals;
run;

proc print data=work.class label;
run;
work.class dataset

Accumulate sub-totals and totals using DATA step statements.

Use the BY statement to aggregate by subgroups.

Here, we will use the sashelp.class dataset as above learning point.


data work.test;
	set sashelp.class;
run;

/* Sorting the dataset using the the 'Sex' column in ascending order*/
proc sort data=work.test;
	by Sex;
run;

/* Using the BY statement to aggregate by subgroups (by Sex) */
proc print data=work.test;
	by Sex;
run;

Use first. and last. processing to identify where groups begin and end.


/* Sorting the dataset using the the 'Sex' column in ascending order*/
proc sort data=sashelp.class out=work.test;
	by Sex;
run;

/* Use first. and last. processing to identify where groups begin and end. */
data work.test;
	set work.test;
	by Sex;
	if first.Sex then first = 1;
	else first = 0; 
	if last.Sex then last = 1;
	else last = 0;
run;

proc print data=work.test;
run;

Use the RETAIN and SUM statements.


/* Using the RETAIN and SUM statements */
/* Here, we accumulate the heights of all the people */
data work.test;
	set sashelp.class;
	retain TotalHeight 0;
	TotalHeight = sum(TotalHeight,Height);
run;

proc print data=work.test;
run;

Use SAS functions to manipulate character data, numeric data, and SAS date values.

Use SAS functions such as SCAN, SUBSTR, TRIM, UPCASE, and LOWCASE to perform tasks such as the tasks shown below:

Replace the contents of a character value.

Trim trailing blanks from a character value.

Search a character value and extract a portion of the value.

Convert a character value to upper or lowercase.


data work.test;
	x = trim('Trailing   '); * Trim trailing blanks from a character value;
	y = scan("Mary had a little lamb",4); * Search a character value and extract a portion of the value;
									      * Here, we are extracting the 4th word 'little'; 
	z = substr("ABCDEFG",2,4); * Extracting 4 characters from the 2nd character onwards ;								      
	a = upcase(y); * Convert a character value to uppercase;
	b = lowcase(y); * Convert a character value to lowercase;
run;

proc print data=work.test;
run;

Output:

Use SAS arithmetic, financial, and probability functions to create or modify numeric values by using the INT and ROUND functions.


data work.test;
	x = int(156.7); * int() strips off the decimal;
	y = round(111.16); * round() rounds the numbers to the nearest integer;
run;

proc print data=work.test;
run;

Output:

Create SAS date values by using the functions MDY, TODAY, DATE, and TIME.


data work.test;
	x = int(156.7); * int() strips off the decimal;
	y = round(111.16); * round() rounds the numbers to the nearest integer;
run;

proc print data=work.test;
run;

Output:

Extract the month, year, and interval from a SAS date value by using the functions YEAR, QTR, MONTH, and DAY.


data work.test;
	* Returns the year from a SAS date value;
	date = '25dec97'd;
	yr = year(date);
	
	* Returns the quarter of the year from a SAS date value;                                                                                                                                                                                                                                
  	x = qtr('20jan94'd);     
  	
  	* Returns the month from a SAS date value;
  	date = '25jan94'd;
	m = month(date);
	
	* Returns the day of the month from a SAS date value;
	now = '05may97'd;
       d = day(now);
run;

proc print data=work.test;
run;

Output:

Perform calculations with date and datetime values and time intervals by using the functions INTCK, INTNX, DATDIF and YRDIF.


data work.test;
	* Returns the number of interval boundaries of a given kind that lie between two dates, times, or datetime values;
	qtr = intck('qtr','10jan95'd,'01jul95'd);
	
	* Increments a date, time, or datetime value by a given time interval, and returns a date, time, or datetime value;                                                                                                                                                                                                                                
  	x = intnx('week', '17oct03'd, 6);     
  	
  	* Returns the number of days between two dates after computing the difference between the dates according to specified day count conventions;
  	sdate = '16oct78'd;
  	edate='16feb96'd;
    actual = datdif(sdate, edate, 'act/act');
	
	* Returns the difference in years between two dates according to specified day count conventions, returns a person’s age;
	sdate = '16oct1998'd;
	edate='16feb2010'd;
    age = yrdif(sdate, edate, 'AGE');
run;

proc print data=work.test;
run;

Output:

Use SAS functions to manipulate character data, numeric data, and SAS date values.

Explain the automatic conversion that SAS uses to convert values between data types.

Use the INPUT function to explicitly convert character data values to numeric values.


data work.test;
	* Explain the automatic conversion that SAS uses to convert values between data types;
	/*
		If you define a numeric variable and assign the result of a character expression to it, SAS tries to convert the character result 
		of the expression to a numeric value and to execute the statement. If the conversion is not possible, SAS prints a note to the log, 
		assigns the numeric variable a value of missing, and sets the automatic variable _ERROR_ to 1.

		If you define a character variable and assign the result of a numeric expression to it, SAS tries to convert the numeric result of 
		the expression to a character value using the BESTw. format, where w is the width of the character variable and has a maximum value 
		of 32. SAS then tries to execute the statement. If the character variable you use is not long enough to contain a character 
		representation of the number, SAS prints a note to the log and assigns the character variable asterisks. If the value is too small, 
		SAS provides no error message and assigns the character variable the character zero (0).
	*/

	* Use the INPUT function to explicitly convert character data values to numeric values;
	input sale $9.;
    fmtsale = input(sale,comma9.);
datalines;
2,115,353
run;

proc print data=work.test;
run;

Output:

Process data using DO LOOPS.

Explain how iterative DO loops function.

Use DO loops to eliminate redundant code and to perform repetitive calculations.

Use conditional DO loops.

Use nested DO loops.


data work.test;
	*Explain how iterative DO loops function;
	/*
		Do loops function by iterating through a counter or list of values and through each iteration of the loop,
		the counter's current value is used throughout the loop function. When it reaches the end, we then use the next
		counter value in the iteration or list and continue the loop function as normal until we exhuast all the counters values.
	*/
	
	*Use DO loops to eliminate redundant code and to perform repetitive calculations;
	/* Summing all the numbers from 1 to 10 */
	sum = 0;
	do i = 1 to 10;
		sum = sum + i;	
	end;
	
	*Use conditional DO loops;
	i = 0;
	sum_2 = 0;
	do while (i <= 10);
		sum_2 = i + sum_2;
		i = i + 1;
	end;
	
	*Use nested DO loops;
	sum_3 = 0;
	do x = 1 to 10;
		do y = 1 to 10;
			sum_3 = sum_3 + x + y;
		end;
	end;
	
	/* Drop i variable */
	drop i x y;
run;

proc print data=work.test;
run;

Output:

Restructure SAS data sets with PROC TRANSPOSE.

Select variables to transpose with the VAR statement.

Rename transposed variables with the ID statement.

Process data within groups using the BY statement.

Use PROC TRANSPOSE options (OUT=, PREFIX= and NAME=).


*Select variables to transpose with the VAR statement;
proc transpose 
 	data = sashelp.shoes 
 	out = work.shoes_tranposed
 	prefix = Value
	name = Category;
	var sales; * sales variables is the only one selected ;
run;

*Rename transposed variables with the ID statement;
proc sort
	data = sashelp.shoes
	out = work.shoes_clean nodupkey;
	by subsidiary product;
run;

proc transpose
	 data = work.shoes_clean
	 out = work.shoes_tranposed2
	 name = Category;
	 var sales;
	 by subsidiary;
	 id product;
run;

*Process data within groups using the BY statement;
proc sort data=sashelp.shoes out=shoes_sorted ;
	by subsidiary;
run; 

proc transpose
	 data = shoes_sorted
	 out = work.shoes_tranposed3
	 prefix = Value
	 name = Category;
	 var sales;
	 by subsidiary;
run;

*Use PROC TRANSPOSE options (OUT=, PREFIX= and NAME=);
proc transpose 
 	data = sashelp.shoes 
 	out = work.shoes_tranposed4
 	prefix = Value
	name = Category;
run;

Output:

partial work.shoes_tranposed output
partial work.shoes_tranposed2 output
partial work.shoes_tranposed3 output
partial work.shoes_tranposed4 output

Use macro variables to simplify program maintenance.

Create macro variables with the %LET statement.

Use macro variables within SAS programs.


* Create macro variables with the %LET statement;
* Use macro variables within SAS programs;
%let yr = 2005;
proc print data=sashelp.shoes;
	title('This is the year ' &yr);
run;

Output:

partial sashelp.shoes output