Order of categories : cartesian product (cross join) of categorical variables

Ronein — Thu, 22 Aug 2019 04:11:16 GMT

Hello

In the following example I produce a data set that includes all possible combinations of 3 categorical variables.

The categorical values are defined in 3 %let statements.

Then I create 3 data sets (each data set contain the categories of one field)

Then I am using cross join (Cartesian product ) to produce the output data set.

In the output data set It is important for me to control the order of the categories.

Required order :

product A product B product C

'free' 'cheap' 'normal' 'expensive'

'low' 'medium' 'high'

My question is why the order is:

Free
Normal
Expensive
Cheap

(I defined that the order will be :

Free
Cheap
Normal
Expensive

)

%let COVa_colA = product A, product B, product C;
%let COVb_colB = free, cheep, normal, expensive;
%let COVc_colC = low, medium, high;


data temp1 (keep=cola)
     temp2 (keep=cola)
     temp3 (keep=cola);
  set sashelp.vmacro (where=(scope eq 'GLOBAL' and prxmatch('/^COV\w_.+$/i',name )));
  i=1;
  do while (scan(value,i) ne "");
    cola=scan(value,i);
    if name =: "COVA" then do;
      cola=catx(" ",cola,scan(value,i+1));
      output temp1;
      i+2;
    end;
    else if name =: "COVB" then do;
      output temp2;
      i+1;
    end;
    else if name =: "COVC" then do;
      output temp3;
      i+1;
    end;
  end;
run;



/*If we want to control the order of rows*/
proc format;
  invalue $orderb
    'free'=1
    'cheap'=2
    'normal'=3
    'expensive'=4;

  invalue $orderc
    'low'=1
    'medium'=2
    'high'=3;
run;


proc sql noprint;
  create table want  as
  select *
    from temp1,
         temp2 (rename=(cola=colb)),
         temp3 (rename=(cola=colc))
      order by cola,
               input(colb,$orderb.),
               input(colc,$orderc.);
  ;
quit;
/*
No of categories in Cat1 :3 (product A, product B, product C)
No of categories in Cat2 :4 (free, cheep, normal, expensive)
No of categories in Cat3 :3 (low, medium, high)
In output table we will get 36 obs beacause 3x4x3=36 
*/

Re: Order of categories : cartesian product (cross join) of categorical variables

Ronein — Thu, 22 Aug 2019 04:17:39 GMT

I found solution to control the order of categories but I still ask how to control it via proc sql?

Why is it not working in proc sql?

data want3;
 set temp1;
 do i=1 to _nobs;
  set temp2(rename=(cola=colb)) nobs=_nobs point=i;
   do j=1 to __nobs;
    set temp3(rename=(cola=colc)) nobs=__nobs point=j;
    output;
   end;
 end;
run;

Re: Order of categories : cartesian product (cross join) of categorical variables

ChrisNZ — Thu, 22 Aug 2019 05:42:02 GMT

>Why is it not working in proc sql?

Because cheep does not have a value in the informat.

topic Re: Order of categories : cartesian product (cross join) of categorical variables in SAS Programming

Order of categories : cartesian product (cross join) of categorical variables

Re: Order of categories : cartesian product (cross join) of categorical variables

Re: Order of categories : cartesian product (cross join) of categorical variables