BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
wurthre
Fluorite | Level 6


I am trying to use the do/until loop to work through every combination of multiple comparisons for a data set of categorical variables with 2+ categories.

I was thinking I would use a do loop to work through them, but am not sure if I am constructing this correctly given for this to work a) they need to be nested so that K (sexuality0 runs through each number first, followed by L (Ques) working through until it reaches the final option. b) I am wondering if there is a way to avoid repeating combinations (so with removal) as I want the  bonferoni correction at the end. I spend a very long time looking for a way to efficiently do multiple comparisons for post hoc chi-square analaysis of categories and came up with nothing---so would super appreciate the help!

 

Caveat: never used a do/until loop so not sure if this can be run outside of a data step. Eventually I plan to make this into a macro, but want to make sure the logic is correct first.

 

Data looks like this with Ques having categories 0-4 and Sexuality from 0-3:
Ques Sexuality
0 1
0 3
1 2
4 1 etc.

 

SAS code:

K = 0; L= 0;
do until(K = 3);
do until(L = 4);
ods output chisq(persist)=chisq(where=(statistic="Chi-Square")rename=(Prob=Raw_P));
proc freq data=Ivf;
where Ques in (K, L);
tables Ques*sexuality / chisq cellchi2 nopercent;
K+ +1;end;
L+ +1;end;
run;
ods output clear;
proc print noobs;
var value raw_p;
run;
ods output clear;
proc print noobs;
var value raw_p;
run;
proc multtest pdata=chisq bon;
run;

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

You still haven't completely converted your code to take advantage of the macro.

For example you have created macro variables K and L but your WHERE statement is not references them. Instead it is trying to reference data set varaibles K and L. To reference the value of a macro varaible you need to use & macro trigger.

where &factor1. in (&k, &L);

There is no need to nest macro definitions. If you want to nest them then just call another macro from within the body of a macro. So if you pull the definition of %SUBCATEGORY() out of the definition of %FACT() you will see that it actually does nothing since it never calls any SAS code or macro code.  You really don't need two macros here unless you add more logic to the outer one.

 

%macro fact
(factor1   /* variable name for the question */
,factor2   /* variable name for the group */
,n         /* Number of categories in FACTOR1 */
,dsn = IVF_2 /* Name of dataset */
);
%local K L ;
* Make sure that Aggregate table does not exist ;
proc delete data=all_chisq; 
run;

%do k = 0 %to &n;
  %do l = &k %to &n;

ods output chisq(persist)=chisq(where=(statistic="Chi-Square")rename=(Prob=Raw_P));

proc freq data=&dsn;
  where &factor1 in (&k &l);
  tables &factor1*&factor2 / chisq cellchi2 nopercent;
run;

* Aggregate individual tables ;
proc append base=all_chisq data=chisq force;
run;

  %end;
%end;

%mend fact;


%fact (Q4,sexuality, 9);

 

View solution in original post

10 REPLIES 10
Kurt_Bremser
Super User

Your logic makes no sense, as you will accept two values for ques in every iteration, but never select for sexuality anyway. And if you only want one value for ques and sexuality (respectively) in the where, the cross-tabulation will make no sense.

 

Do is a data step statement only. But before you repeat code with a macro (%do), try by-group processing first.

wurthre
Fluorite | Level 6

You are right! I altered the overall logic when I tried to redo the macro I had been working through:

 

%macro subcategory(sub1, sub2);
K = 0;
do until(K = &sub2.);
L = K+1;
do until(K = &sub2. );
ods output chisq(persist)=chisq(where=(statistic="Chi-Square")rename=(Prob=Raw_P));
proc freq data=Ivf;
         where &factor1. in (K, L);
         tables &factor1.*&factor2. / chisq cellchi2 nopercent;
         run;
%mend subcategory;

Here it is clearer as what I am actually trying to accomplish is to have every combination 0-x (x being the number of subcategories in a factor (Ques) placed within those parenthises. So (0,1) (0,2) (0,3) etc. but am struggling with how to capture ones like (2,3). I feel like this is a matter of how to order or maybe nest the do loop so that it runs through K (0-9) and then indexing 1 while also having L index 1 each time.  I might be totally off, & if so I would really appreciate some thoughts about a better way!

wurthre
Fluorite | Level 6

Thank you! That is very helpful. My only remaining question is where to place these %do loops.

 

I realize it needs to be inside of my macro, but when I do this it appears to conflict with the order of calling the macros. I nest it this way because I then want to do the opposite (look within factor2) within the same %macro fact.  Do you happen to know if the %do macros need placed elsewhere for the other outer macros to work? I've done nested macros similar to %macro fact & %macro subcategory before so I am struggling to figure out why it won't run. Thank you so much!!

 

%macro fact(factor1, factor2);

%macro subcategory(sub2);
%do k = 0 %to &sub2.;
  %do l = &k. %to &sub2.;

ods output chisq(persist)=chisq(where=(statistic="Chi-Square")rename=(Prob=Raw_P));
proc freq data=Ivf_2;
         where &factor1. in (k, l);
         tables &factor1.*&factor2. / chisq cellchi2 nopercent;
         run;
  %end;
%end;
%mend subcategory;

%mend fact;

%subcategory (9);/*(lowest number, highest number for subcategory within a question)*/

%fact (Q4,sexuality); /*factor1 is the variable name for the question; factor2 is variable name for group i.e country*/
Tom
Super User Tom
Super User

You still haven't completely converted your code to take advantage of the macro.

For example you have created macro variables K and L but your WHERE statement is not references them. Instead it is trying to reference data set varaibles K and L. To reference the value of a macro varaible you need to use & macro trigger.

where &factor1. in (&k, &L);

There is no need to nest macro definitions. If you want to nest them then just call another macro from within the body of a macro. So if you pull the definition of %SUBCATEGORY() out of the definition of %FACT() you will see that it actually does nothing since it never calls any SAS code or macro code.  You really don't need two macros here unless you add more logic to the outer one.

 

%macro fact
(factor1   /* variable name for the question */
,factor2   /* variable name for the group */
,n         /* Number of categories in FACTOR1 */
,dsn = IVF_2 /* Name of dataset */
);
%local K L ;
* Make sure that Aggregate table does not exist ;
proc delete data=all_chisq; 
run;

%do k = 0 %to &n;
  %do l = &k %to &n;

ods output chisq(persist)=chisq(where=(statistic="Chi-Square")rename=(Prob=Raw_P));

proc freq data=&dsn;
  where &factor1 in (&k &l);
  tables &factor1*&factor2 / chisq cellchi2 nopercent;
run;

* Aggregate individual tables ;
proc append base=all_chisq data=chisq force;
run;

  %end;
%end;

%mend fact;


%fact (Q4,sexuality, 9);

 

wurthre
Fluorite | Level 6

Oh...that's great to know! I think I've been thinking in terms of nested macros for awhile now & probably only just now came across a time when that is an issue. My frustration probably still remains with the %do loop as I've yet to create one that works ---& I feel as though it'd be quite powerful 

wurthre
Fluorite | Level 6

Again, this is SOOO helpful. Especially clarifying the process. It still has errors, but they pertain to "k", and I cannot figure out why that is. You even defined k as being local to the macro & yet this is the error. If you can think of why it seems to not be accepting the %do portion than that would be awesome! But, you've already been a tremendous help 🙂

31        proc freq data=&dsn;   where &factor1 in (k, l);
                                                    -
                                                    22
                                                    76
ERROR: Syntax error while parsing WHERE clause.

ERROR 22-322: Syntax error, expecting one of the following: a quoted string, a numeric constant,
              a datetime constant, a missing value, -.
Tom
Super User Tom
Super User

If you want to use the value of a macro variable to generate SAS code then you need to expand the value by using &.

 where &factor1 in (&k, &l);
mkeintz
PROC Star

If your original data set is big enough to make repeated proc freq's expensive, then do a proc freq against it justg once, while creating the data set ALL_FREQS per below.  It will have one record per every combination of factor1*factor2 plus the new variable COUNT. 

 

Then loop  proc freq through that data set, weighting by count, to get your chi-square values.  I.e. something like:

 

 

proc freq data=&dsn  noprint ;
  where &factor1 in (&k &l);   /* editted correction - should have removed this line */
  tables &factor1*&factor2 / out=all_freqs ;
run;

 

%do k = 0 %to &n;
  %do l = &k %to &n;

ods output chisq(persist)=chisq(where=(statistic="Chi-Square")rename=(Prob=Raw_P));

proc freq data=all_freqs  ;
  where &factor1 in (&k , &L);
  tables &factor1*&factor2 / chisq cellchi2 nopercent;

  weight COUNT;
run;
* Aggregate individual tables ;
proc append base=all_chisq data=chisq force;
run;

  %end;
%end;

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 2874 views
  • 2 likes
  • 4 in conversation