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

Hi everyone.

 

I have the following two sets of variables:

 

                 A1_1                A1_2          A1_3..........A1_10                     

                 A2_1                A2_2          A2_3..........A2_10

                    .                          .                 .                   .

                    .                          .                 .                   . 

                    .                          .                 .                   .             

                 A10_1               A10_2         A10_3       A10_10   where the possible categories are 1 = good, 2 = better,

                                                                                               and 3 = best

                                  

                           

                             

                 B1_1                B1_2          B1_3..........B1_10                     

                 B2_1                B2_2          B2_3..........B2_10

                    .                          .                 .                   .

                    .                          .                 .                   . 

                    .                          .                 .                   .             

                 B10_1               B10_2         B10_3       B10_10   where the possible categories are 1 = yes and 2 = no

 

I need to pair up two variables and do a PROC FREQ:

                   

                     A1_1 and B1_1

                     A1_2 and B1_2

                     A1_3 and B1_3

                      .....

                     A10_10 and B10_10 where I just need to get 3 = best and 1 = yes.

 

How do I code this in such a way I don't have to do multiple PROC FREQ and SORT commands?

 

Thank you very much.

 

           

1 ACCEPTED SOLUTION

Accepted Solutions
mkeintz
PROC Star

You apparently want to produce100 cross tabulations:  A1_1*B1_1 A1_2*B1_2 .... A10_10*B10_10.As @Reeza said a macro can save you from typing out 100 crosstab expressions:

 

%macro xtab_list;
   %do i=1 %to 10; %do j=1 %to 10;  A&i._&j * B&i._&j %end;%end;
%mend xtab_list;

proc freq data=have;
  table %xtab_list ;
run;
--------------------------
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

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

View solution in original post

5 REPLIES 5
Reeza
Super User

Sort isn't necessary for PROC FREQ but I suspect a macro is what you'll need here. You may also want to think about how you want to store your data. I suspect you don't want to see all that output in the results window. 

 

A macro loop is relatively easy, but how you want to store the results does affect how it's designed. It may also b easier to restructure your data. Set the second number to be an variable and then do a cross between A and B.

 


@yoyong wrote:

Hi everyone.

 

I have the following two sets of variables:

 

                 A1_1                A1_2          A1_3..........A1_10                     

                 A2_1                A2_2          A2_3..........A2_10

                    .                          .                 .                   .

                    .                          .                 .                   . 

                    .                          .                 .                   .             

                 A10_1               A10_2         A10_3       A10_10   where the possible categories are 1 = good, 2 = better,

                                                                                               and 3 = best

                                  

                           

                             

                 B1_1                B1_2          B1_3..........B1_10                     

                 B2_1                B2_2          B2_3..........B2_10

                    .                          .                 .                   .

                    .                          .                 .                   . 

                    .                          .                 .                   .             

                 B10_1               B10_2         B10_3       B10_10   where the possible categories are 1 = yes and 2 = no

 

I need to pair up two variables and do a PROC FREQ:

                   

                     A1_1 and B1_1

                     A1_2 and B1_2

                     A1_3 and B1_3

                      .....

                     A10_10 and B10_10 where I just need to get 3 = best and 1 = yes.

 

How do I code this in such a way I don't have to do multiple PROC FREQ and SORT commands?

 

Thank you very much.

 

           


 

yoyong
Obsidian | Level 7

Hi @Reeza,

 

Not sure what you meant by "Set the second number to be an variable and then do a cross between A and B".

 

As for the output, I would want something like this:

 

                        PAIR                                    CATEGORIES                         YES       NO                                    

                   A1_1 , B1_1                                      Good                                  

                                                                             Better

                                                                             Best 

 

Thanks.

 

 

 

Reeza
Super User

@yoyong wrote:

Hi @Reeza,

 

Not sure what you meant by "Set the second number to be an variable and then do a cross between A and B".

 

As for the output, I would want something like this:

 

                        PAIR                                    CATEGORIES                         YES       NO                                    

                   A1_1 , B1_1                                      Good                                  

                                                                             Better

                                                                             Best 

 

Thanks.

 

 

 


What about the next step? Are you looking for just the results into a result/HTML window or a data set output?

mkeintz
PROC Star

You apparently want to produce100 cross tabulations:  A1_1*B1_1 A1_2*B1_2 .... A10_10*B10_10.As @Reeza said a macro can save you from typing out 100 crosstab expressions:

 

%macro xtab_list;
   %do i=1 %to 10; %do j=1 %to 10;  A&i._&j * B&i._&j %end;%end;
%mend xtab_list;

proc freq data=have;
  table %xtab_list ;
run;
--------------------------
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

--------------------------
ballardw
Super User
data _null_;
   call execute ('Proc Freq data=have;');
   length string $ 75;
   do i= 1 to 10;
      do j=1 to 10;
      string = catx(' ','tables',catt('A',i,'_',j),'*',catt('B',i,'_',j),';' );
      call execute(string);;
      end;
   end;
   call execute ('run;');
run;

Will do the tables.

 

I suspect you might A10_10 and B10_10 where I just need to get 3 = best and 1 = yes. as a separate Proc Freq call using a WHERE statement to subset the data.

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 1738 views
  • 2 likes
  • 4 in conversation