Standardized tables with repeated labels using PROC TABULATE in SAS

2
votes

In SAS, I need a PROC TABULATE where labels are repeated so that it's easier on Excel to find them using INDEX-MATCH. Here is an example with sashelp.cars.

The first PROC TABULATE has the advantage of having repeating labels, which is needed for the INDEX-MATCH. But, its flaw is that SAS only gives the non missing values. 

data cars;
set sashelp.cars;  
run;

proc sort data=cars;
by make;
run;

This doesn't give all labels. I would like a table with 3 continents by column (Europe, Asia, USA) and every car type (Sedan, SUV, Wagon, Sports...).

PROC TABULATE DATA = cars;
option missing=0;
by make;
CLASS make type Type Origin / mlf MISSING  ; 
TABLE (
(type*make)
), (Origin='')   / printmiss nocellmerge ; RUN;

So, in order to have all the 3 continents by colum, and every type of car (Sedan, SUV, Wagon, Sports...), I use CLASSDATA, as suggested:

Data level; 
    set cars;
    keep make type Type Origin;
Run;

PROC TABULATE DATA = cars MISSING classdata=level;
    option missing=0;
    by make;
    CLASS make type Type Origin / mlf MISSING  ; 
    TABLE (
    (make*type)
    ), (Origin='')   / printmiss nocellmerge ; 
RUN;    

Data level; 
    set cars;
    keep make type Type Origin;
Run;

PROC TABULATE DATA = cars MISSING classdata=level;
    option missing=0;
    by make;
    CLASS make type Type Origin / mlf MISSING  ; 
    TABLE (
    (make*type)
    ), (Origin='')   / printmiss nocellmerge ; 
RUN;

But this gives a humongous table, and non repeating labels. Is there a midway solution with :

  1. all the columns (3 continents) like in the last table
  2. only the concerned MAKEs, that is the first 6 rows for Acura
  3. repeated labels like in the first PROC TABULATE

Thank you very much,

1
excelsastabulate
Read about the out= option on proc tabulate in documentation.sas.com/…. Then proc export the dataset you create instead. (And if you succeed, answer your own question, so others can bennifit from it. If you don't I share that with us, so we can help.) - Dirk Horsten
You need to provide template structure to proc tabulate using CLASSDATA and/or PRELOADFMT. I recommend CLASSDATA in this situation - check the docs for more details. - Reeza
You are making it difficult in two ways: 1) You insist on using output from proc tabulate meant for human reading as input for another program i.c. excel formulas). You would better use typical data processing procedures, like proc means, sql create table and proc transpose. 2) You insist on using excel match functions, which goes in error if is does not find the data it looks for. - Dirk Horsten

1 Answers

1
votes

I advice not exporting the listing of proc tabulate to excel

proc tabulate does not repeat values in the first column for each value in the second, because the output is meant for human reading. This is not the tool you need to write data to excel for further lookup.

I advice not using MATCH but SUMIFS

MATCH is a great function in excel, but is not a good choice for your application, because

  • it gives an error when it does not find what you look for, and that is why you need all labels in your output
  • it only supports one criterion, so you need at least 3 of them
  • it returns a position, so you still need an index function.

Therefore, I advice writing a simple create table

PROC sql;
   create table TO_EXPORT as 
   select REGION, MACTIV, DATE, count(*) as cnt
   from data
   group by REGION, MACTIV, DATE;
proc export data = TO_EXPORT file="&myFolder\&myWorkbook..xlsx" replace;
RUN;

you will have your data in Excel in a more data oriented format.

To retrieve the data, I advise the following type of excel formula

=sumifs($D:$D,$A:$A,"13-*",$B:$B,$C:$C,"apr2020")`

It adds all counts with left of them the criteria you are looking for. Because at most one row will meet these criteria, it actually just looks up a count you are looking for. If that count does not exist, it will just return zero.

Disclaimer: I did not test this code, so if it does not work, leave a comment and I will.