[Proc Tabulate] How to repeat the same lines of a table ?

[phil27]

Hello,
I submit the following code to perform a table.

[pre]
proc tabulate DATA=query;
class Metier EmploiEnClair Niveau;
TABLE Metier*EmploiEnClair', Niveau*n='';
run;
[/pre]

This produces :

.................................................Niveau1
Metier............EmploiEnClair

.....................Architecte d'intérieur...10
Architecte......Chargé d'études..........25
.....................Responsable...............2

4 records have value "Architecte" for the field "Metier".

Instead of having one value of "Architecte" merged for the 4 lines I would like to have "Architecte" repeated 4 times (one per line) :

.................................................Niveau1
Metier............EmploiEnClair

Architecte......Architecte d'intérieur...10
Architecte......Chargé d'études..........25
Architecte......Responsable...............2

I can't find how to to this with a proc tabulate.

It would be possible to do it with a proc sql but I have tables with percentages and it is easied to calculate them with a proc tabulate.

Thanks. Message was edited by: phil27

[Cynthia_sas]

Hi:
At this point, you have a couple of choices:
1) use TABULATE to create your output and percentages, but also use the OUT= option on the PROC TABULATE statement:
[pre]
proc tabulate DATA=query out=work.tabout;
... rest of tabulate code...

proc print data=work.tabout;
run;
[/pre]

to create an output dataset with the statistics you want. Then, use the output dataset in a PROC PRINT to show the value of the CLASS variable on every report row. Do note that the output structure of the OUT= dataset will not look the same as the original TABULATE output and the columns will be named differently, so you'll have to assign labels in your PROC PRINT, but that is one approach.

2) The other approach depends on how comfortable you feel with PROC REPORT. You can use a COMPUTED variable in PROC REPORT to repeat values on report rows, such as you want. However if you have never used PROC REPORT before, that may be harder to do and if you are doing complicated percentages, those are easier to do with TABULATE than with REPORT -- for some percents, you might need another COMPUTE block with PROC REPORT.

TABULATE is designed for nested class variables to display as you describe, so there is just no way to repeat values of Metier on every summarized row for EmploiEnClair in TABULATE.

cynthia

[phil27]

Hello,
great, many thanks for your answers.

Phil

[Peter_C]

no feature of tabulate does this.
How about
proc tabulate out= tabul1 data= query;
class Metier EmploiEnClair Niveau;
TABLE Metier*EmploiEnClair', Niveau*n='';
run;
* It is like proc means but with percentages.
* and it must hold all class values on each row of the OUT= table;
proc print ;
run ;

Then you have to print the resulting percentages,
and replace all the formatting that is so good with tabulate

Here (with no special formatting) is a demo using sashelp.class[pre]proc tabulate data= sashelp.class out= tabul1 ;
class sex age ;
var weight ;
table sex*age, weight*(n='n'*f=3. pctsum='%') ;
run;
proc print label ;
id sex age ;
var weight_n weight_pctsum_00 ;
sum weight_n weight_pctsum_00 ;
run ; [/pre]and the results[pre]tabulate out=

Weight_
PctSum_
Sex Age Weight_N 00

F 11 1 2.657
F 12 2 8.498
F 13 2 9.576
F 14 2 10.129
F 15 2 11.813
M 11 1 4.473
M 12 3 16.338
M 13 1 4.420
M 14 2 11.313
M 15 2 12.891
M 16 1 7.893
======== =======
19 100.000[/pre]

good luck
peterC

[ballardw]

Maybe not the most elegant solution but I have made similar tables by concatenating the two (or more) variables in another data set.

Data tab;
set query;
/* probably need some LENGTH and other character stuff to make this pretty*/
metier_emploi = metier || emploienclair;
run;

Proc tabulat data=tab;
class metier_emploi Niveau;
TABLE metier_emploi , Niveau*n='';
run;