BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Yurie
Fluorite | Level 6

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! 

                             

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

8 REPLIES 8
Reeza
Super User
Do you need a table or report in PDF/HTML? I think Proc tabulate can do it directly from your data.
Yurie
Fluorite | Level 6

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

ballardw
Super User

Percent of what?

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

Yurie
Fluorite | Level 6

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! 

ballardw
Super User

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.

Yurie
Fluorite | Level 6

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

Ksharp
Super User

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;
Yurie
Fluorite | Level 6

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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