DATA Step, Macro, Functions and more

Complex Summary Report

Accepted Solution Solved
Reply
Contributor
Posts: 30
Accepted Solution

Complex Summary Report

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! 

                             


Accepted Solutions
Solution
‎01-06-2016 07:17 PM
Super User
Posts: 10,516

Re: Complex Summary Report

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.

View solution in original post


All Replies
Super User
Posts: 17,863

Re: Complex Summary Report

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

Re: Complex Summary Report

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

Super User
Posts: 10,516

Re: Complex Summary Report

Percent of what?

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

Contributor
Posts: 30

Re: Complex Summary Report

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! 

Solution
‎01-06-2016 07:17 PM
Super User
Posts: 10,516

Re: Complex Summary Report

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.

Contributor
Posts: 30

Re: Complex Summary Report

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

Super User
Posts: 9,682

Re: Complex Summary Report

I notice something wrong in 

 

                                                           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;
Contributor
Posts: 30

Re: Complex Summary Report

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 289 views
  • 1 like
  • 4 in conversation