SAS Arrays

SAS Arrays : Introduction

It provides a simple, appropriate way to process a group of variables in a SAS DATA step.

Syntax

Array array-name {number-of-elements} list-of-variables;

Note: You can use [ ] or { } or ( ) for defining number of elements in the ARRAY statement.

Examples

1.  ARRAY ABC[5] a b c d e; 

In the example above, ABC is an array-name, 5 implies the number of variables in array and "a b c d e" are the fields that make up the array.

2. ARRAY ABC[*] a b c d e; 

In the example above, SAS would automatically calculate the number of variables in array.

3. ARRAY ABC[*] X1-X10;

Where the X1 variable contains the X1 value, X2 contains the X2 value, etc.

4. ARRAY ABC[*]  $ X1-X10;

If the variables are of character type then use $ sign before specifying list of variables.

Sample Data

SAS Array : Example

data temp;
input x1 x2 x3 x4$ x5$;
cards;
1 2 3 AA BB
2 3 4 AB CC
3 4 5 AC DD
4 5 6 AD EE
5 6 7 AE FF
6 7 8 AF GG
;
run;

Example I : Numeric variables having value greater than 3 need to be replaced with missing value

data test;
set temp;
array nvars {3} x1-x3;
do i = 1 to 3;
if nvars{i} > 3 then nvars{i} =.;
end;
run;  

Output : Array Statement

Why i is 4 in the output data set?

The first time the loop processes, the value of count is 1; the second time, 2; and the third time, 3. At the beginning of the fourth iteration, the value of count is 4, which is found to be greater than the stop value of 3 so the loop stops. However, the value of i is now 4 and not 3, the last value before it would be greater than 3 as the stop value.

Note : We can drop variable "i" with drop statement or drop data set option.

Improvised version of the above code 

data test;
set temp;
array nvars (*) _numeric_;
do i = 1 to dim(nvars);
if nvars{i} > 3 then nvars{i} =.;
end;
drop i;
run;

Notes - 

1. The "_numeric_" is used to specify all the numeric variables.
2. The DIM function returns the number of elements (variables).

Example II. : Extract first letter of all the character variables

data test;
set temp;
array cvars (*) _character_;
do i = 1 to dim(cvars);
cvars{i} = substr(cvars{i},1,1);
end;
drop i;
run;

 Note - The "_character_" is used to specify all the character variables.

Example III. : Extract first letter and fill in the new character variables

data test;
set temp;
array cvars (*) _character_;
array dvars (*) $ x6 X7;
do i = 1 to dim(cvars);
dvars{i} = substr(cvars{i},1,1) ;
end;
drop i;
run;

Example IV : Assign Initial Values in a SAS Array

data abcd;
set temp;
array nvars (*) _numeric_;
array pvars (*) px1 px2 px3;
array pctinc {3} _temporary_ (1.1 , 1.2 ,1.3); do i = 1 to dim(nvars);
pvars{i} = nvars{i} * pctinc{i};
end;
drop i;
run;  

Notes -

1. In the above example, we are multiplying variables' values with different numbers.
2. When the key word _TEMPORARY_ is used in a ARRAY statement, data elements are created but are not stored in the data file.

Example V : Calculate Percentage Growth

data abcd;
set temp;
array nvars(*) _numeric_;
array diff{2} _temporary_;
array percent{2};
do i = 1 to 2;
diff{i} = nvars{i +1} - nvars{i};
percent{i} = diff{i} / nvars{i} ;
end;
drop i;
run;

Using the OF Operator in a SAS Array

Examples :

array gnp (*) x y z;

The following two statements are equivalent.

sumgnp = sum(of gnp(*)) ;
sumgnp = sum(x,y,z); 

*Calculate the mean;
  mean_score = mean(of gnp(*));

* Calculate the minimum;
   min_score = min(of gnp(*));

DO OVER LOOP

The DO OVER loop is one of the most useful DO loops. It can be used with an array when indexing of the array is not needed.

data test;
set temp;
array nvars _numeric_;
do over nvars;
if nvars > 3 then nvars = .;
end;
run;

                     
     SOURCE                          

Cartesian Product

1) Cartesian Product

A simplest join in proc sql where the final out is each row of first dataset combined with each row of the second. Its syntax is:

Proc sql;

      create table company as

            select a.empid, b.name, b.salary

            from employee as a , salary as b

            ;

Quit;

Here the final output will be product of all rows from the employee dataset with all rows in the salary dataset.

Practically this looks wrong as everyone’s salary will be everyone others’ salary, perfect Socialism J

But surprisingly this Cartesian product is the basis of all joins. Whenever you specify a join a Cartesian product is done and the output rows are restricted by certain conditions. So knowing this is necessary.

2) Cartesian Product through a datastep

This technique is not very intuitive but is asked in a lot of interviews so I am including it here:

data every_combination;

  /* Set one of your data sets, usually the larger data set */

  set one;

  do i=1 to n;

    /* For every observation in the first data set,    */

    /* read in each observation in the second data set */

    set two point=i nobs=n;

    output;

  end;

 run;

This technique creates a product of all rows.

So above is the hint for answering the question in merging SAS datasets with different id variables names. Let’s see whether you can find the answer.

for more click here or click here

Proc SQL joins

A Proc SQL join works same as a datastep merge to combine two or more SAS datasets. There are many types of SQL joins which can be used based on your output requirements.

1) Inner Join

An inner join provides only the matching rows from both datasets. Its syntax is:

Proc sql;

      create table company as

            select a.empid, b.name, b.salary

            from employee as a inner join salary as b

            where a.empid=b.emp_id;

Quit;

Here the final output will be all the employees having their salary information in the salary dataset. If the info for a employee is found in any one of the datasets then that employee will not be present in the final table.

1a) Inner Join with datastep:

The same inner join can be performed with a datastep merge.

data company;

  merge employee(in=a) salary(rename=(emp_id=empid) in=b);

  by empid;

  if a and b;

run;

We required the rename as datastep cannot perform a merge on id variables with different names.

Actually there is a way to merge by differently named id variables but that method is at from the notion of elegant coding and is just developed as a workaround as you can always rename id variables easily.

OK OK If you require it so much I will discuss the method towards the end.

2) Outer Join (Left)

A Left Outer join outputs the matching rows from both datasets as well as it also outputs the non-matching from the left dataset or the dataset specified first in the query. Its syntax is:

Proc sql;

      create table company as

            select a.empid, b.name, b.salary

            from employee as a left outer join salary as b

            where a.empid=b.emp_id;

Quit;

Here the final output will be all the employees whether or not they have their salary information in the salary dataset. If you want to list all the employees irrespective of their salary being updated in the salary dataset then you will use left outer join.

2a) Left outer Join with datastep:

The same join can be performed with a datastep merge. We just need to change the IF condition.

data company;

  merge employee(in=a) salary(rename=(emp_id=empid) in=b);

  by empid;

  if a;

run;

3) Outer Join (Right)

A Right Outer join outputs the matching rows from both datasets as well as it also outputs the non-matching from the right dataset or the dataset specified second in the query. Its syntax is:

Proc sql;

      create table company as

            select a.empid, b.name, b.salary

            from employee as a right outer join salary as b

            where a.empid=b.emp_id;

Quit;

Here the final output will be all the employees who have their salary information in the salary dataset as well as the employees who are not currently updated in employee dataset but their salary info was updated. The situation looks silly but in this case you will be using a right outer join.

One point to note here is that we are taking empid from the employee dataset so for these non-matching employees of the salary dataset the empid will be missing which is definitely not desirable. So as a precaution we generally use the COALESCE function in right outer joins.

Proc sql;

      create table company as

            select coalesce(a.empid,b.emp_id), b.name, b.salary

            from employee as a left outer join salary as b

            where a.empid=b.emp_id;

Quit;

This will solve the problem of missing employee ids.

3a) Right Outer Join with datastep:

The right outer join can be performed with a datastep merge similarly as the left outer.

data company;

  merge employee(in=a) salary(rename=(emp_id=empid) in=b);

  by empid;

  if b;

run;

4) Full Join

A Full join as you have rightly guessed by now outputs the matching rows from both datasets as well as it also outputs the non-matching from both the datasets. Its syntax is:

Proc sql;

      create table company as

            select coalesce(a.empid,b.emp_id), b.name, b.salary

            from employee as a full join salary as b

            where a.empid=b.emp_id;

Quit;

Here the final output will be all the employees who have their salary information either in the salary dataset or the employee dataset.

Here also we use COALESCE function or the same reason as in Right outer join.

4a) Full Join with datastep:

The full join can also be performed with a datastep merge. We just need to eliminate the if condition or for better understanding we can keep the condition as IF A OR B;

data company;

  merge employee(in=a) salary(rename=(emp_id=empid) in=b);

  by empid;

  if a or b;

run;

                   for more    click here