topic Re: Generate multiple tables in a loop in SAS Enterprise Guide

Generate multiple tables in a loop

ayin — Thu, 02 Mar 2017 00:38:34 GMT

Currently using the following codes to generate one set of tables:

%let Num_category = 3;

%macro table;
%do _j=1 %to &Num_category;

proc tabulate data=input.data out=output.sum_classA_typeA_&_j;   /* use the same input dataset */
      class v1;
      var Period_1 - Period_23;
      where class = 'classA' and type = 'typeA';  /* specify class and type here */
      table (v1 =''), (Period_1 - Period_23) * (sum='No.') *f=comma9.2 / box ='name' printmiss;
quit;

data output.sum_classA_typeA_&j;
set output.sum_classA_typeA_&j;
...             
run;

proc sort data=output.sum_classA_typeA_&j;
by ID;
run;
%end;
%mend table;
%table;

Now the problem is, there are more classes and types than just 'classA' and 'typeA'.

Is there a way to create new loops to run the macro above?

One possible approach might be creating two arrays:

1. classA classB classC;

2. typeA typeB typeC;

Each time will take one value from array1 and one value from array2, and generate a new set of tables: e.g.

output.sum_classA_typeB_&j;

and then keep going:

output.sum_classA_typeC_&j;
output.sum_classB_typeA_&j;
...

It's very easy to achieve this task in other languages (e.g. R, python), not sure how to do it in SAS enterprise guide?

Thanks.

Re: Generate multiple tables in a loop

Reeza — Thu, 02 Mar 2017 00:56:39 GMT

Have you looked into expanding your BY group processing?

@ayin wrote:

It's very easy to achieve this task in other languages (e.g. R, python), not sure how to do it in SAS enterprise guide?

Thanks.

Find the best way to do something in SAS. R/Python function as matrix type languages while SAS processed data row by row. EG tends to be more SQL but if you're a programmer I don't recommend the GUI tools. This means that (most of) my SAS code that processes 100 rows will work for 1 million or 100 million as long as I can wait a bit, without further changes to account for data size. It's not as important these days since RAM has become so cheap but was very important once and in many places where getting upgrades is difficult it's still important.

Re: Generate multiple tables in a loop

ayin — Thu, 02 Mar 2017 01:09:07 GMT

Hey Reeza, thanks for your reply.
The objectivity here is to create multiple sets of tables so they can be used in the future.

Also the users need to pre-determine what classes/types they will be using. (The input dataset has more than 10 classes and more than 10 types, but we only want to generate tables for 3 classes and 3 types. i.e. 9 sets of tables instead of 100 sets).

Re: Generate multiple tables in a loop

Reeza — Thu, 02 Mar 2017 01:15:25 GMT

Given your response I strongly suggest you revisit BY GROUP processing. The number of tables created doesn't matter, simply apply the criteria as a filter.

Obviously this can be done with loops, it's just not efficient and cumbersome to manage that code.

Re: Generate multiple tables in a loop

Reeza — Thu, 02 Mar 2017 01:28:38 GMT

If you decide to continue on a macro route:

1. Here's a link to the SAS macro examples available in the documentation

https://communities.sas.com/t5/SAS-Communities-Library/SAS-9-4-Macro-Language-Reference-Has-a-New-Appendix/ta-p/291716

2. You'll likely find CALL EXECUTE helpful to call the macro, see the documentation examples for how to do this from parameters in a dataset.

Re: Generate multiple tables in a loop

ayin — Thu, 02 Mar 2017 05:33:13 GMT

Thanks Reeza for your suggestion.

Regarding the question, I have found one solution:

%let Class = classA classB classC;                /* determine the classes */
%let Type = typeA typeB typeC;                   /* determine the types   */
%let Num_class = %sysfunc(countw(&Class));
%let Num_type = %sysfunc(countw(&Type));

%global Class_Name;
%global Type_Name;

%macro test;
%do _k=1 %to &Num_class;
%let Class_Name = %scan(&Class,&_k);      /* retrieve the value */

%do _l=1 %to &Num_type;
%let Type_Name = %scan(&Type,&_l);

proc tabulate data=input.data out=output.sum_&Class_Name._&Type_Name._&_j;
...
          where class = symget('Class_Name') and type = symget('Type_Name');
...
quit;

%end;
%end;
%mend test;
%test;

Re: Generate multiple tables in a loop

Reeza — Thu, 02 Mar 2017 06:51:52 GMT

If you post the full proc tabulate code I can show you how to do this without a macro.

Re: Generate multiple tables in a loop

rogerjdeangelis — Thu, 02 Mar 2017 17:31:49 GMT

Tabulations and output NAMED output datasets for every combination of car make and drivetrain

HAVE
===

Up to 40 obs WORK.CARS total obs=428

Obs    TYPE      ORIGIN    MAKE     DRIVETRAIN

  1    SUV       Asia      Acura      All
  2    Sedan     Asia      Acura      Front
  3    Sedan     Asia      Acura      Front
  4    Sedan     Asia      Acura      Front
  5    Sedan     Asia      Acura      Front
  6    Sedan     Asia      Acura      Front
  7    Sports    Asia      Acura      Rear
  8    Sedan     Europe    Audi       Front
  9    Sedan     Europe    Audi       Front
 10    Sedan     Europe    Audi       Front
 11    Sedan     Europe    Audi       All


WANT Tabulations and datasets
=============================

Tabulations for all

make and drivetrain combinations for Hybrid and Asia

--------------------------------------------
|                |       DRIVETRAIN        |
|                |-------------------------|
|                |          Front          |
|                |-------------------------|
|                |     N      |    PctN    |
|----------------+------------+------------|
|MAKE            |            |            |
|----------------|            |            |
|Honda           |        2.00|      100.00|
|----------------+------------+------------|
|Toyota          |        1.00|      100.00|
--------------------------------------------

....

make and drivetrain combinations for Truck and Asia

----------------------------------------------------------------------
|                |                    DRIVETRAIN                     |
|                |---------------------------------------------------|
|                |           All           |          Rear           |
|                |-------------------------+-------------------------|
|                |     N      |    PctN    |     N      |    PctN    |
|----------------+------------+------------+------------+------------|
|MAKE            |            |            |            |            |
|----------------|            |            |            |            |
|Mazda           |        1.00|      100.00|        1.00|      100.00|
|----------------+------------+------------+------------+------------|
|Nissan          |        2.00|      100.00|           .|           .|
|----------------+------------+------------+------------+------------|
|Subaru          |        1.00|      100.00|           .|           .|
|----------------+------------+------------+------------+------------|
|Toyota          |        1.00|      100.00|        2.00|      100.00|
----------------------------------------------------------------------

 Also want these datasets

                   Member
 #  Name           Type

 1  HYBRID_ASIA    DATA
 2  HYBRID_EUROPE  DATA
 3  HYBRID_USA     DATA
 6  SEDAN_ASIA     DATA
 7  SEDAN_EUROPE   DATA
 8  SEDAN_USA      DATA
 9  SPORTS_ASIA    DATA
 0  SPORTS_EUROPE  DATA
 1  SPORTS_USA     DATA
 2  SUV_ASIA       DATA
 3  SUV_EUROPE     DATA
 4  SUV_USA        DATA
 5  TRUCK_ASIA     DATA
 6  TRUCK_EUROPE   DATA
 7  TRUCK_USA      DATA
 8  WAGON_ASIA     DATA
 9  WAGON_EUROPE   DATA
 0  WAGON_USA      DATA


Sample datsets TRUCK_ASIA

 Up to 40 obs from TRUCK_ASIA total obs=6

 Obs     MAKE     DRIVETRAIN    _TYPE_    _PAGE_    _TABLE_    N    PCTN_11

  1     Mazda        All          11         1         1       1      100
  2     Mazda        Rear         11         1         1       1      100
  3     Nissan       All          11         1         1       2      100
  4     Subaru       All          11         1         1       1      100
  5     Toyota       All          11         1         1       1      100
  6     Toyota       Rear         11         1         1       2      100


WORKING CODE
===========

    DOSUBL
       select distinct (quote(type))   into :types separated   by "," from sashelp.cars;
       select distinct (quote(origin)) into :origins separated by "," from sashelp.cars
    DOSUBL
       proc tabulate data=sashelp.cars out=&typ._&org;
        title "make and drivetrain combinations for &typ and &org";
        where type="&typ" and origin="&org";


FULL SOLUTION
=============

* create some data;
data cars;
  retain  type origin make drivetrain;
  set sashelp.cars(keep=make drivetrain type origin);
run;quit;

%symdel types origins; * just in case they exist;
proc datasets lib=work kill;
run;quit;

data _null_;

   * get the metat data;
   if _n_ = 0 then do;
      %let rc=%sysfunc(dosubl('
         proc sql;
            select distinct (quote(type))   into :types separated   by "," from sashelp.cars;
            select distinct (quote(origin)) into :origins separated by "," from sashelp.cars
         ;quit;
      '));
   end;

   set sashelp.cars(obs=1);

     do typ=&types;
       do org=&origins;
         call symputx('typ',typ);
         call symputx('org',org);
         rc=dosubl('
            proc tabulate data=sashelp.cars out=&typ._&org;
              title "make and drivetrain combinations for &typ and &org";
              where type="&typ" and origin="&org";
              class make drivetrain;
              table make,drivetrain*(n pctn<drivetrain>)/rts=18;
            run;quit;
         ');
       end;
     end;
   stop;
run;quit;