Faster way to do proc freq for 2 arrays?

[gwilli31]

I am trying to check for errors in an array I created by using a proc freq. I was able to figure out a way to run the procedure and get the result I want, but I'm wondering if there is a faster way to do it in the future. I have 2 arrays with 17 variables, so the code I used was:

proc freq data=culture; tables binary1*CB1 binary2*CB2 binary3*CB3 binary4*CB4 binary5*CB5 binary6*CB6
binary7*CB7 binary8*CB8 binary9*CB9 binary10*CB10 binary11*CB11 binary12*CB12 binary13*CB13 binary14*CB14
binary15*CB15 binary16*CB16 binary17*CB17/ list missing; run;

As you can see, it's pretty long and it was tedious to type it all out, but it returned exactly what I wanted. Is there a faster way to do this? Some things I tried that didn't work:

proc freq data=culture; tables binary:*CB: / list missing; run;

proc freq data=culture; tables binary1*CB1--binary17*CB17 / list missing; run;

If anyone can either explain why those other options didn't work and/or provide an alternative approach, I'd really appreciate it!

[data_null__]

Transpose your arrays so that you have a BY variable for the array index and variables BINARY and CB;

The run 

proc freq;
   by INDEX;
   tables BINARY*CB / list missing;
   run;

[ballardw]

I am going to correct your statement of "didn't work for this version:

proc freq data=culture; tables binary:*CB: / list missing; run;

That works, as in runs, and creates all of the combinations of the variables. You just don't like that it produces way more output than you want to wade through.

I am correcting this in case someone finds this thread and thinks the code will not run or is flawed. Perhaps the crosses of all the variables is what that hypothetical searcher might actually want.

Note, for data quality you can suppress the percentages by adding the options NOROW NOCOL NOPERCENT.

[gwilli31]

Thanks for clarifying, yes both of the other methods run and produce results, just not the results that I want.

[Reeza]

In this specific case there is no shortcut notation due to the naming scheme.
Transposing and then using BY group processing is dynamic and would work regardless of the number of N.
The only way otherwise to automate/simplify this would be to use a macro loop.

[ballardw]

Here is an example of the macro approach suggested by @Reeza ;

%macro ccc (base1=,base2=,count=1);
   %do i=1 %to &count;
   &base1.&i * &base2.&i
   %end;
%mend;

/* you can run this to see what is created by the macro*/
%put value= %ccc(base1=varx,base2=vary,count=3);

/* how to use*/
proc freq data=culture ;
  tables %ccc(base1=binary , base2=cb, count=17) / list missing;
run;

I couldn't come up with a nice meaningful name quickly so just called the macro CCC for Combining some stuff (3 parameters). Note very carefully that the third line of the macro does not end in a semicolon as that is the text generated to use in other places. You would not want a tables statement that looks like:

Tables varx1 * vary1 ; varx2 * vary2;

which would be the result if that line has a semicolon.

Caution: this approach requires the variables to have the same number of elements. If you 15 of one array and 14 of the other some reason then this is not the approach to use (back to manually writing all that code).

Note that editors make such easy if you put each pair on one line in the tables statement:

tables

binary1*cb1

binary2*cb2

binary3*cb3

/list missing

;

Copy the first pair and paste as separate lines as many times as needed. The change the editor setting from insert to overwrite and go down the list replacing 1 with 2, 3, ... 17. If you do this much you will get in the habit of allowing a space or two before the * so the overwrite for longer numbers fits easily.

Depending on the editor you may even find that the down arrow key moves the cursor to the number below without having to space back.

You could provide default values for the base variables and the count such as appear in the proc freq code. Those would be used as defaults and the call would look like %ccc();