topic Re: Complex Summary Report in SAS Programming

Complex Summary Report

Yurie — Wed, 06 Jan 2016 17:59:41 GMT

Hello, everyone

I have a database structured as:

C_Name Status_code Status Month Count

C1 1 H Jan 70

C1 1 H Feb 67

C1 2 S May 61

C2 1 H Jan 1

C2 1 H Mar 20

C2 2 S Feb 30

C2 2 S Mar 21

C2 3 P Apr 30

C2 4 F May 41

My report should looks like:

Jan Feb Mar Apr May ..............

C_name Status_code status count percent count percent count percent count percent count percent ............

C1 1 H 70 100% 67 100%

C1 2 S 61 100%

subtotal 70 100% 67 100% 61 100%

C2 1 H 1 100% 20 40%

C2 2 S 30 100% 30 60%

C2 3 P 30

C2 4 F 41 100%

subtotal 1 100% 30 100% 50 100% 30 100% 41 100%

I am appreciate for any suggestions. Thank you!

Re: Complex Summary Report

Reeza — Wed, 06 Jan 2016 18:02:44 GMT

Do you need a table or report in PDF/HTML? I think Proc tabulate can do it directly from your data.

Re: Complex Summary Report

Yurie — Wed, 06 Jan 2016 18:26:06 GMT

Thank you for your response. I need an excel file for my final report.

Re: Complex Summary Report

ballardw — Wed, 06 Jan 2016 20:46:42 GMT

Percent of what?

Also your results show a Mar with count 30 for C2,2,S but the input data shows 21.

Re: Complex Summary Report

Yurie — Wed, 06 Jan 2016 21:00:45 GMT

I need a percentage of count from the subtotal for each C_name and each month. The count for Mar should be 21. Thanks for your correction. I am very appreciate for any help!

Re: Complex Summary Report

ballardw — Wed, 06 Jan 2016 21:02:18 GMT

Assuming percent refers to the percent of the total of C_name and that the 30 that appears in March is a typo in either the data input or example output with associated error in % calculation I get this as a start:

data have;
   input C_Name $  Status_code  Status $  Month $  Count  ;
   RepDate =  input(cats(month,'2015'),monyy.);
datalines;
C1              1                  H          Jan      70
C1              1                  H          Feb      67
C1              2                  S          May      61
C2              1                 H           Jan       1
C2              1                 H           Mar      20
C2              2                 S           Feb      30
C2              2                 S           Mar      21
C2              3                 P           Apr      30
C2              4                 F           May      41      
;
run;

Proc sort data=have; by repdate;run;

proc tabulate data=have;
   class c_name status status_code;
   class month /order=data;
   var count;
   table c_name *(status_code*status All='C_name total'),
         month=''*count=''*(sum='Count' pctsum<c_name*status_code all>='percent')
         / misstext=' ' ;
run;

Because the Jan, Feb, Mar are not going to sort in any easy way I add the RepDate variable so the values can be sorted into actual date order and then use the ORDER option on the class statement for month to appear in the table in the order the value appear on the input dataset.

The bit in <> is to provide the denominator for the percentage calculation. Since the subtotal is totaling the values of count for all of the Status_codes (my assumption) within C_name then C_name Status_code and the All have to appear similar to the appearance in the start of the table statement.

Lots of examples on this formum and in the documentation for using any of a variety of ODS methods to sandwich around the tabulate code to generate your output file.

Re: Complex Summary Report

Yurie — Thu, 07 Jan 2016 00:17:57 GMT

Thank you so much for your help. Yes. This is the result I need. Blessings! -Yurie

Re: Complex Summary Report

Ksharp — Thu, 07 Jan 2016 03:02:20 GMT

I notice something wrong in ballardw 's output.

Jan Feb Mar Apr May ..............

C_name Status_code status count percent count percent count percent count percent count percent ............

C1 1 H 70 100% <- 98.59 from ballardw 67 100%

C1 2 S 61 100%

subtotal 70 100% 67 100% 61 100%

C2 1 H 1 100% <- 1.41 from ballardw 20 40%

C2 2 S 30 100% 30 60%

C2 3 P 30

C2 4 F 41 100%

subtotal 1 100% 30 100% 50 100% 30 100% 41 100%

data have;
   input C_Name $  Status_code  Status $  Month $  Count  ;
   RepDate=  input(cats(month,'2015'),monyy.);
datalines;
C1              1                  H          Jan      70
C1              1                  H          Feb      67
C1              2                  S          May      61
C2              1                 H           Jan       1
C2              1                 H           Mar      20
C2              2                 S           Feb      30
C2              2                 S           Mar      21
C2              3                 P           Apr      30
C2              4                 F           May      41      
;
run;

Proc sort data=have; by repdate;run;
proc sql;
create table want as
 select a.*,a.Count/(select sum(count) from have where C_Name=a.C_Name and Month=a.Month) as percent format=percent8.2
  from have as a;
quit;
proc tabulate data=want;
   class c_name status status_code;
   class month /order=data;
   var count percent;
   table c_name *(status_code*status All='C_name total'),
         month=''*(count=''*sum='Count'*f=best. percent=''*sum='percent'*f=percent.)
         / misstext=' ' ;
run;

Re: Complex Summary Report

Yurie — Fri, 08 Jan 2016 16:17:29 GMT

Great! The percentage is correct! Thank you so much for your help! Blessings! -Yurie