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 

Convert all Numeric Variables to Character using proc SQL

The task is to create a successor to a SAS data set, replacing each numeric variable in the original with a character variable. Here is a method which preserves variable order and variable attributes. First create a data set for the demonstration:

proc sql;

create table mixedtype as

 select *

  from sashelp.class;

quit;

Result:

                  Age as
                    of 1
 First             March
 Name      Sex      2008    Height  Weight
 -----------------------------------------
 Alfred    M          14        69  112.50
 Alice     F          13      56.5   84.00
 Barbara   F          13      65.3   98.00
 Carol     F          14      62.8  102.50
 Henry     M          14      63.5  102.50
 James     M          12      57.3   83.00
 Jane      F          12      59.8   84.50
 Janet     F          15      62.5  112.50
 Jeffrey   M          13      62.5   84.00
 John      M          12        59   99.50
 Joyce     F          11      51.3   50.50
 Judy      F          14      64.3   90.00
 Louise    F          12      56.3   77.00
 Mary      F          15      66.5  112.00
 Philip    M          16        72  150.00
 Robert    M          12      64.8  128.00
 Ronald    M          15        67  133.00
 Thomas    M          11      57.5   85.00
 William   M          15      66.5  112.00

Next, this somewhat tricky query which uses DICTIONARY.COLUMNS as a source of metadata and creates the bulk of another query:

proc sql;

select case type

        when 'num'  then catx (   ' '

                                , 'left( put ('

                                , name

                                , ','

                                , case

                                   when format is null then 'best12.'

                                   else format

                                   end

                                , ') ) as'

                                , name

                                , 'label ='

                                , quote( strip(label) )                              

                              )

        when 'char' then name

        else             catx (   ' '

                                , quote('Error on type')

                                , 'as'

                                , name

                              )

        end

 into : selections separated by ' , '

 from dictionary.columns

 where libname='WORK' and memname='MIXEDTYPE';

To adapt for other tables change only the literals in the WHERE clause.

Result:

 Name
 Sex
 left( put ( Age , best12. ) ) as Age label = "Age as of 1 March 2008"
 left( put ( Height , best12. ) ) as Height label = ""
 left( put ( Weight , 6.2 ) ) as Weight label = ""

The INTO clause takes these expressions and concatenates them into a comma-separated string which it stores in the macro variable SELECTIONS, which in turn is used in the statement which actually does the work:

proc sql;

create table allchar as select &selections from mixedtype;

quit;

Result:

 First          Age as of 1
 Name      Sex  March 2008    Height        Weight
 -------------------------------------------------
 Alfred    M    14            69            112.50
 Alice     F    13            56.5          84.00
 Barbara   F    13            65.3          98.00
 Carol     F    14            62.8          102.50
 Henry     M    14            63.5          102.50
 James     M    12            57.3          83.00
 Jane      F    12            59.8          84.50
 Janet     F    15            62.5          112.50
 Jeffrey   M    13            62.5          84.00
 John      M    12            59            99.50
 Joyce     F    11            51.3          50.50
 Judy      F    14            64.3          90.00
 Louise    F    12            56.3          77.00
 Mary      F    15            66.5          112.00
 Philip    M    16            72            150.00
 Robert    M    12            64.8          128.00
 Ronald    M    15            67            133.00
 Thomas    M    11            57.5          85.00
 William   M    15            66.5          112.00

Another query against DICTIONARY.COLUMNS compares the attributes of the original and replacement variables:

 select   name    format=$10.

        , memname format=$10.

        , varnum  format=10.

        , type    format=$8.

        , length  format=6.

        , format  format=$8.

        , label   format=$30.

  from dictionary.columns

  where libname='WORK' and memname in ('ALLCHAR','MIXEDTYPE')

  order by varnum, memname desc;

 quit;

Result:

                             Column
 Column      Member       Number in  Column    Column  Column
 Name        Name             Table  Type      Length  Format    Column Label
 --------------------------------------------------------------------------------------

 Name        MIXEDTYPE            1  char           8            First Name

 Name        ALLCHAR              1  char           8            First Name

 Sex         MIXEDTYPE            2  char           1  $1.

 Sex         ALLCHAR              2  char           1  $1.

 Age         MIXEDTYPE            3  num            8            Age as of 1 March 2008

 Age         ALLCHAR              3  char          12            Age as of 1 March 2008

 Height      MIXEDTYPE            4  num            8

 Height      ALLCHAR              4  char          12

 Weight      MIXEDTYPE            5  num            8  6.2

 Weight      ALLCHAR              5  char           6

This demonstrates that variable order and variable attributes are preserved.