topic Re: create dummy code matrices from all combinations of a set of stratifying variables in SAS Programming

create dummy code matrices from all combinations of a set of stratifying variables

acerickson — Wed, 20 Sep 2017 21:43:08 GMT

Hello,
I'm a SAS noob (using SAS 9.4) and couldn't find an existing question to satisfy my question, so here I am.

I need to create a series of dummy variable matrices of several categorical variables for all possible combinations of a set of stratifying variables. In the simplified example below, I input the 4 actual stratifying variables and possible values (a-d), and 2 categorical variables (ms3c and edu2c). If you run the code below, the resulting datasets (test2, test3) represent what I will want as output but as separate csv files. SHould end up with 840 different matrices.

I have been try to include a 'allcomb' function in a DO Loop, but can't figure out the syntax.
Thanks for any help.

data test1; 
input uid a b c d ms3c edu2c;
cards;
1 1 1 1 1 2 2
2 1 2 1 1 3 2
3 1 3 3 3 3 1
4 2 4 4 4 2 1
5 1 5 5 5 1 2
6 2 6 1 6 2 1
7 1 7 2 7 1 2
8 2 8 3 6 3 2
9 1 9 4 5 3 1
10 2 10 5 4 2 2
11 2 11 1 3 2 2
12 1 12 2 2 1 2
13 1 1 1 1 3 1
14 1 2 1 1 2 2
15 2 3 5 3 2 1
run;

* this gives an idea of what I need to do, create 1 matrix for every combination of strata variables; 
data test2 (drop=uid a b c d ms3c edu2c);
 set test1;
 if a=1 and b=1 and c=1 and d=1 and ms3c=2 then ms2=1; else ms2=0;
 if a=1 and b=1 and c=1 and d=1 and ms3c=3 then ms3=1; else ms3=0;
 if a=1 and b=1 and c=1 and d=1 and edu2c=2 then edu2=1; else edu2=0;
 * export to csv;
run;
*;
data test3 (drop=uid a b c d ms3c edu2c);
 set test1;
 if a=1 and b=2 and c=1 and d=1 and ms3c=2 then ms2=1; else ms2=0;
 if a=1 and b=2 and c=1 and d=1 and ms3c=3 then ms3=1; else ms3=0;
 if a=1 and b=2 and c=1 and d=1 and edu2c=2 then edu2=1; else edu2=0;
* export to csv;
run;
* ... and so on ...;

Re: create dummy code matrices from all combinations of a set of stratifying variables

Reeza — Wed, 20 Sep 2017 21:45:38 GMT

First question, why are you doing this?

Second:

I've listed a few of the options here:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-dummy-variables-Categorical-Variables/ta-p/308484

Do any of those work for you?

@acerickson wrote:

Hello,
I'm a SAS noob (using SAS 9.4) and couldn't find an existing question to satisfy my question, so here I am.

I need to create a series of dummy variable matrices of several categorical variables for all possible combinations of a set of stratifying variables. In the simplified example below, I input the 4 actual stratifying variables and possible values (a-d), and 2 categorical variables (ms3c and edu2c). If you run the code below, the resulting datasets (test2, test3) represent what I will want as output but as separate csv files. SHould end up with 840 different matrices.

I have been try to include a 'allcomb' function in a DO Loop, but can't figure out the syntax.
Thanks for any help.
data test1; 
input uid a b c d ms3c edu2c;
cards;
1 1 1 1 1 2 2
2 1 2 1 1 3 2
3 1 3 3 3 3 1
4 2 4 4 4 2 1
5 1 5 5 5 1 2
6 2 6 1 6 2 1
7 1 7 2 7 1 2
8 2 8 3 6 3 2
9 1 9 4 5 3 1
10 2 10 5 4 2 2
11 2 11 1 3 2 2
12 1 12 2 2 1 2
13 1 1 1 1 3 1
14 1 2 1 1 2 2
15 2 3 5 3 2 1
run;

* this gives an idea of what I need to do, create 1 matrix for every combination of strata variables; 
data test2 (drop=uid a b c d ms3c edu2c);
 set test1;
 if a=1 and b=1 and c=1 and d=1 and ms3c=2 then ms2=1; else ms2=0;
 if a=1 and b=1 and c=1 and d=1 and ms3c=3 then ms3=1; else ms3=0;
 if a=1 and b=1 and c=1 and d=1 and edu2c=2 then edu2=1; else edu2=0;
 * export to csv;
run;
*;
data test3 (drop=uid a b c d ms3c edu2c);
 set test1;
 if a=1 and b=2 and c=1 and d=1 and ms3c=2 then ms2=1; else ms2=0;
 if a=1 and b=2 and c=1 and d=1 and ms3c=3 then ms3=1; else ms3=0;
 if a=1 and b=2 and c=1 and d=1 and edu2c=2 then edu2=1; else edu2=0;
* export to csv;
run;
* ... and so on ...;

Re: create dummy code matrices from all combinations of a set of stratifying variables

acerickson — Wed, 20 Sep 2017 22:03:27 GMT

thanks for the quick reply.

it's in order to perform an indirect adjustment in a Cox model using two distinct but representative datasets. The model has 4 stratifying variables (sex, 5-year age groups, urban size and airshed).

I don't think those suggestions will work easily as I need the program to loop through all possible combinations of the stratifying variables.

Re: create dummy code matrices from all combinations of a set of stratifying variables

ballardw — Wed, 20 Sep 2017 22:14:21 GMT

1) I don't think that your "and so on" after data test3 is as obvious as you think it may be.

I am a tad concerned about resue of the dummy variable names ms2, ms3 and edu2. Same variable name with different meanings will almost always lead to some confusion.

I find the easiest, though not the most elegant, way to get "all combinations" of multiple variables is nested arrays. I can't actually tell whay your limits are on each variable but this should demonstrate:

data example;
   /* variable a to have ranges of 1,2,3
      variable b to have range of 88,99
      variable c to have range of 'AB', 'AC', 'EX'
   */
   do a= 1 to 3;
      do b = 88,99;
         do c = 'AB','AC','EX';
         output;
         end;
      end;
   end;
run;

The example provides a mix of sequential integers, non-sequential integers and a character variable. The order of the nesting isn't really import though that output statement is critical.

How you use those to make your dummies is up to you. I would likely be tempted to put the code for that in the innermost do loop (BEFORE the output), but that would require different names from the way you did it.

Re: create dummy code matrices from all combinations of a set of stratifying variables

Reeza — Wed, 20 Sep 2017 22:17:48 GMT

Can we assume you're familiar with how SAS handles CLASS and STRATA variables then?

And that SAS automatically creates the dummy variables for all levels present in the data already?

Re: create dummy code matrices from all combinations of a set of stratifying variables

acerickson — Wed, 20 Sep 2017 22:42:09 GMT

thanks for the response, that a helpful start. I can use different variable names, that's no problem.

apologies for the unclear description.

There are 4 stratifying variables with the following ranges:

a (1,2)

b (1,12)

c (1,5)

d (1,7)

so what I mean by the "..and so on..." comment, is that I would need to repeat those 3 IF statements 840 times in order to cycle through all possible combinations of the 4 stratifying variables.

thanks again!

Re: create dummy code matrices from all combinations of a set of stratifying variables

acerickson — Wed, 20 Sep 2017 23:31:15 GMT

yes, that is within the PROC statement for the Cox model (e.g. proc phreg).

I'm calculating a series of Cox models, then taking a summed ratio of them (see attached for a summary slide).

Re: create dummy code matrices from all combinations of a set of stratifying variables

Astounding — Thu, 21 Sep 2017 00:38:21 GMT

Really just a shot in the dark here, since it's too difficult for me to figure out what you will do with this later. Can't you just use a single variable that takes on 840 different values? The matrix would be:

data all_combinations;

do a=1 to 2;

do b=1 to 12;

do c=1 to 5;

do d=1 to 7;

single_variable = d + 10*c + 100*b + 10000 * a;

output;

end; end; end; end;

run;

You need 10,000 (not 1,000) as the multiplier for A, since B can be two digits.

Re: create dummy code matrices from all combinations of a set of stratifying variables

Reeza — Thu, 21 Sep 2017 02:44:18 GMT

Couldn't you just put them into the BY statement or does that mess up something else?

Note that STRATA is different than BY so if you're including them in the STRATA this isn't possible.

As long as you have data for each stratum SAS will process it appropriately.

@acerickson wrote:

thanks for the response, that a helpful start. I can use different variable names, that's no problem.

apologies for the unclear description.

There are 4 stratifying variables with the following ranges:

a (1,2)

b (1,12)

c (1,5)

d (1,7)

so what I mean by the "..and so on..." comment, is that I would need to repeat those 3 IF statements 840 times in order to cycle through all possible combinations of the 4 stratifying variables.

thanks again!

Re: create dummy code matrices from all combinations of a set of stratifying variables

acerickson — Thu, 21 Sep 2017 17:54:31 GMT

thanks for the input, I'm trying to think how to possibly make this work... if I was able to assign the new 'single_variable' to the corresponding individual records based on their values for the variables a to d, that would be something.

Re: create dummy code matrices from all combinations of a set of stratifying variables

acerickson — Thu, 21 Sep 2017 18:06:27 GMT

thanks again for your time and effort on this, I'm not really making it easier for you folks.

thinking about it more, I think Astounding might be onto something - I should be able to do a one-to-many merge of the unique 'single_variable' back into my main dataset matching on the 4 strata variables, correct? This could solve a couple issues, like ensuring at least one event (death) occurs in each strata, and if there are any unpopulated strata.

Re: create dummy code matrices from all combinations of a set of stratifying variables

acerickson — Thu, 21 Sep 2017 18:08:04 GMT

thinking about it more, I think you might be onto something - I should be able to do a one-to-many merge of the unique 'single_variable' back into my main dataset matching on the 4 strata variables, correct?

Re: create dummy code matrices from all combinations of a set of stratifying variables

Astounding — Thu, 21 Sep 2017 18:09:00 GMT

Couldn't you just use the same formula for SINGLE_VARIABLE, using A, B, C, and D that are found in your data?

Re: create dummy code matrices from all combinations of a set of stratifying variables

acerickson — Thu, 21 Sep 2017 21:47:30 GMT

yes, exactly. This is great as it solves a couple other questions, but still need to create the matrices.

So now I have this 'all_combinations' variable that identifies which of the 840 strata an individual belongs to, so now I can run a BY statement to create the matrices.

Re: create dummy code matrices from all combinations of a set of stratifying variables

acerickson — Thu, 21 Sep 2017 23:49:21 GMT

so close to solving this, just need help with the syntax using a FILE statement with the filevar= option.

Was trying to follow the steps from the FILE statement page here.

* create a new variable identifying the unique strata;
data all_combinations;
do a=1 to 2;
do b=1 to 12;
do c=1 to 5;
do d=1 to 7;
   single_variable = d + 10*c + 100*b + 10000 * a;
   output;
end; end; end; end;
run;

* now merge that back to the main dataset;
proc sort data=test1;
  by a b c d;
run;
proc sort data=all_combinations;
  by a b c d;
run;
data merge1;
  merge test1 all_combinations;
  by a b c d;
run;

/* now create the separate matrices based on the distinct 840 strata 
using a FILE statement to export to a CSV and dynamically update the 
filename to reflect its strata */
* may need to convert 'single_variable' to a character (from numeric);

data test4 (keep= ms2 ms3 edu2);
  set merge1;
  if ms3c=2 then ms2=1; else ms2=0;
  if ms3c=3 then ms3=1; else ms3=0;
  if edu2c=2 then edu2=1; else edu2=0;
  by single_variable;
  file file-specification <device-type> filevar=sinlge_variable; * need help here;
run;

Re: create dummy code matrices from all combinations of a set of stratifying variables

Astounding — Fri, 22 Sep 2017 10:01:01 GMT

A few notes then ...

When using FILEVAR=, the file-specification is just a place holder. You can use any name not already defined by your program, such as _out_.

The device-type is optional and rarely used. Just remove it.

The general idea is to create a character variable holding the complete path to the output file you would like to use. For example:

destination = 'C:\myfolder\combination' || put(single_variable, z5.) || '.txt';

Then complete the FILE statement with:

filevar = destination;

Re: create dummy code matrices from all combinations of a set of stratifying variables

acerickson — Sat, 23 Sep 2017 00:01:20 GMT

still having issues with creating the separate files (couldn't get the FILE statement to work properly)

/* want to create a series of tables that loop through
the values of a variable (single_variable) and name it
with the corresponding value with the prefix "xmat_". */

**** for example *** ;
proc sql ; 
    create table xmat_10111 as select *
    FROM test4
   where single_variable=10111;
* repeat where single_variable =10111 to 21257;
*******************************************;

* what I have so far is;

%macro trial;
%do i=10111 %to 21257;
proc sql ; 
	create table xmat_%i as select *
	FROM test4
	where single_variable=%i;
%end;

%mend ;

Re: create dummy code matrices from all combinations of a set of stratifying variables

Astounding — Sat, 23 Sep 2017 00:43:39 GMT

Be glad you didn't get this to work. Remember SINGLE_VARIABLE only takes on 840 values. If this had worked, you would have created about 10,000 empty tables.

One thing to fix (before we abandon this approach) is that you refer to a macro variable with an ampersand: &i rather than %i

A better way to generate 840 CREATE statements, assuming you have that data set ALL_COMBINATIONS:

data _null_;

call execute('proc sql'; ) ;

do until (done);

set all_combinations end=done;

dsname = put(single_variable, z5.);

call execute('create table xmat_' || dsname || ' as select * from test4 where single_variable= ' || dsname || ';' ) ;

end;

call execute('quit;' ) ;

stop;

run;

Re: create dummy code matrices from all combinations of a set of stratifying variables

Reeza — Sat, 23 Sep 2017 00:58:04 GMT

I feel like we're missing something giant here. Are you then exporting the data out to do the regressions in some other tool? Otherwise, why split the files in the first place or create the single indicator, SAS will handle all of that directly into a BY.

Same thing with the export, you don't need to precalculate it, just sort your data by A/B/C/D and then create the files dynamically using the data step as illustrated.

Re: create dummy code matrices from all combinations of a set of stratifying variables

acerickson — Mon, 25 Sep 2017 20:03:53 GMT

Brilliant! Thank you*840 to Astounding.

So putting it all together we have the following. Perhaps there is a more efficient program, but I am happy with this.

* create a new variable identifying the unique strata;
data all_combinations;
    do a=1 to 2;
    do b=1 to 12;
    do c=1 to 5;
    do d=1 to 7;
        single_variable = d + 10*c + 100*b + 10000 * a;
   output;
end; end; end; end;
run;

* now merge that back to the main dataset (test1);
proc sort data=test1;
    by a b c d;
run;
proc sort data=all_combinations;
    by a b c d;
run;
data merge1;
    merge test1 all_combinations;
    by a b c d;
run;

* create the dummy codes by strata for the variables (ms3c and edu2c);
data test2 (keep= single_variable ms2 ms3 edu2);
  set merge1;
    if ms3c=2 then ms2=1; else ms2=0;
    if ms3c=3 then ms3=1; else ms3=0;
    if edu2c=2 then edu2=1; else edu2=0;
run;

* now create a dummy code matrix table for each unique strata (840) and name the tables with the strata id code (single_variable);
data _null_;
call execute('proc sql') ;
do until (done);
   set test2 end=done;
   dsname = put(single_variable, z5.);
   call execute('create table xmat_' || dsname || ' as select * from test4 where single_variable= ' || dsname || ';' ) ;
end;
call execute('quit;' ) ;
stop;
run;