topic Re: Subtotal percentage on a proc tabulate in New SAS User

Subtotal percentage on a proc tabulate

rimob — Tue, 29 Jan 2019 09:06:41 GMT

Hello,

i have a proc tabulate procedure which i have 12 generations from 201801 to 201812 and i want to calculate for each generation the percentage as :

9/152=...%

72/152=..%

i tried this code :

PROC TABULATE
DATA=work.QUERY_REQ_VDR3
;
CLASS Generation / ORDER=UNFORMATTED MISSING;
CLASS magazin / ORDER=UNFORMATTED MISSING;
CLASS SCOREFEU / ORDER=UNFORMATTED MISSING;
TABLE
/* ROW Statement */
(magazin *(SCOREFEU all = 'Total' )
all = 'Total' ) ,
/* COLUMN Statement */
(Generation all = 'Total')*
(N*f=8.
pctn<SCOREFEU*Generation
SCOREFEU*all
all*Generation all> ='Subtotal Percent'*f=8.4) / rts=20;
;
;
RUN;

RUN; QUIT;
TITLE; FOOTNOTE;"

but is not working to calculate this thing for each SCORE.

could you help me with a solution please ?

Thank you for the help!

Re: Subtotal percentage on a proc tabulate

RW9 — Tue, 29 Jan 2019 09:44:14 GMT

Post test data in the form of a datastep:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

Why does your "Total" = sum(n column) and sum(subgroup percentage)? What is it a percentage of?

Re: Subtotal percentage on a proc tabulate

rimob — Tue, 29 Jan 2019 10:04:40 GMT

I tried to run the following code as you suggested to me :

*************** TEST*******************;
data WORK.CLASS(label='Student Data');
infile datalines dsd truncover;
input Name:$8. Sex:$1. Age:32. Height:32. Weight:32.;
datalines;
Alfred M 14 69 112.5
Alice F 13 56.5 84
Barbara F 13 65.3 98
Carol F 14 62.8 102.5
Henry M 14 63.5 102.5
;;;;
run;

*data set you want to create demo data for;
%let dataSetName = sashelp.Class;
*number of observations you want to keep;
%let obsKeep = 5;

******************************************************
DO NOT CHANGE ANYTHING BELOW THIS LINE
******************************************************;

%let source_path = https://gist.githubusercontent.com/statgeek/bcc55940dd825a13b9c8ca40a904cba9/raw/865d2cf18f5150b8e887218dde0fc3951d0ff15b/data2datastep.sas;

filename reprex url "&source_path";
%include reprex;
filename reprex;

option linesize=max;
%data2datastep(dsn=&dataSetName, obs=&obsKeep);

*****************************;"

but i have the following in LOG :

1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='Program';
4 %LET _CLIENTPROJECTPATH='C:\Users\rami.bayassi\Desktop\SCRIPT ANNUAL ANALYSIS\script_cereri_oney_final_28012019.egp';
5 %LET _CLIENTPROJECTNAME='script_cereri_oney_final_28012019.egp';
6 %LET _SASPROGRAMFILE=;
7
8 ODS _ALL_ CLOSE;
9 OPTIONS DEV=ACTIVEX;
NOTE: Procedures may not support all options or statements for all devices. For details, see the documentation for each procedure.
10 GOPTIONS XPIXELS=0 YPIXELS=0;
11 FILENAME EGSR TEMP;
12 ODS tagsets.sasreport12(ID=EGSR) FILE=EGSR STYLE=Analysis STYLESHEET=(URL="file:///C:/Program%20Files/SAS/SharedFiles/BIClientStyles/4.2/Analysis.css") NOGTITLE NOGFOOTNOTE GPATH=&sasworklocation ENCODING=UTF8 options(rolap="on");
NOTE: Writing TAGSETS.SASREPORT12(EGSR) Body file: EGSR
13
14 GOPTIONS ACCESSIBLE;
15
16 *data set you want to create demo data for;
17 %let dataSetName = sashelp.Class;
18 *number of observations you want to keep;
19 %let obsKeep = 5;
20
21
22 ******************************************************
23 DO NOT CHANGE ANYTHING BELOW THIS LINE
24 ******************************************************;
25
26 %let source_path = https://gist.githubusercontent.com/statgeek/bcc55940dd825a13b9c8ca40a904cba9/raw/865d2cf18f5150b8e887218dde0fc3951d0ff15b/data2datastep.sas;
27
28 filename reprex url "&source_path";
ERROR: The connection has timed out..
ERROR: Cannot open %INCLUDE file REPREX.
29 %include reprex;
30 filename reprex;
NOTE: Fileref REPREX has been deassigned.
31
32 option linesize=max;
WARNING: Apparent invocation of macro DATA2DATASTEP not resolved.
33 %data2datastep(dsn=&dataSetName, obs=&obsKeep);
_
180

ERROR 180-322: Statement is not valid or it is used out of proper order.

34
35 GOPTIONS NOACCESSIBLE;
36 %LET _CLIENTTASKLABEL=;
37 %LET _CLIENTPROJECTPATH=;
38 %LET _CLIENTPROJECTNAME=;
39 %LET _SASPROGRAMFILE=;
40
41 ;*';*";*/;quit;run;
2 The SAS System 16:08 Monday, January 28, 2019

42 ODS _ALL_ CLOSE;
43
44
45 QUIT; RUN;
46

What i should change?

Thank you for your help.

Re: Subtotal percentage on a proc tabulate

RW9 — Tue, 29 Jan 2019 10:14:13 GMT

You can pop the web address:

https://gist.githubusercontent.com/statgeek/bcc55940dd825a13b9c8ca40a904cba9/raw/865d2cf18f5150b8e887218dde0fc3951d0ff15b/data2datastep.sas

Directly into your broswer. This will load the text into the browser. Right click on the text and Save as to a local file (remember to remove .txt from the end so the filetype is .sas. Then include that local file as:

%include "\\pathtoyourlocalfile\data2datastep.sas";

Obviously change pathtoyourlocalfile with the actual place where you saved it.

Re: Subtotal percentage on a proc tabulate

rimob — Tue, 29 Jan 2019 10:38:08 GMT

is working, could you explain to me with what is suppose to help me this test ?

i have the following CODE :

TITLE;
TITLE1 "SCORE by each store &luna.";
PROC TABULATE
DATA=work.QUERY_REQ_VDR3
;
CLASS Generation / ORDER=UNFORMATTED MISSING;
CLASS magazin / ORDER=UNFORMATTED MISSING;
CLASS SCORE / ORDER=UNFORMATTED MISSING;
TABLE
/* ROW Statement */
(magazin *
(SCORE
all = 'Total' )
all = 'Total' ) ,
/* COLUMN Statement */
Generation * N
all = 'Total' * N ;
;
;
RUN;

RUN; QUIT;
TITLE; FOOTNOTE;"

i generated this table below for each store:

and i want to calculate the percentage for each score as :

for 201801:

O=9/152=..%

R=72/152=..%

V=71/152=..%

but with my first code, the calculation is not correct.

which function of % in proc tabulate you think i should use.?

Thank you.

Re: Subtotal percentage on a proc tabulate

ballardw — Tue, 29 Jan 2019 15:58:40 GMT

@rimob wrote:

is working, could you explain to me with what is suppose to help me this test ?

The data set code generated by the macro is expected to be pasted into a code box such as is opened on the forum with the {I} icon so that we can create a data set to test your existing code and to text suggested code to provide workable solutions.

and i want to calculate the percentage for each score as :

for 201801:

O=9/152=..%

R=72/152=..%

V=71/152=..%

That looks like you want the COLPCTN statistic requested along with N

Maybe

PROC TABULATE DATA=work.QUERY_REQ_VDR3
;
   CLASS Generation / ORDER=UNFORMATTED MISSING;
   CLASS magazin / ORDER=UNFORMATTED MISSING;
   CLASS SCORE / ORDER=UNFORMATTED MISSING;
   TABLE
      /* ROW Statement */
      (magazin *
      (SCORE 
      all = 'Total' )
      all = 'Total' ) ,
      /* COLUMN Statement */
      (Generation All='Total')*( N colpctn )
     
   ;
RUN;

Notice that pasting code in the code box will preserve formatting making it easier to read.

Re: Subtotal percentage on a proc tabulate

rimob — Wed, 30 Jan 2019 15:56:58 GMT

@ballardw
Thank you for your answer but unfortunately is not working, the calculation is not right for example:

it should calculate 9/152 but the calculation is 9/Total_generaion.

Do you have another solution please ?

Thanks.