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!
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.
Thank you for your response. I need an excel file for my final report.
Percent of what?
Also your results show a Mar with count 30 for C2,2,S but the input data shows 21.
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!
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.
Thank you so much for your help. Yes. This is the result I need. Blessings! -Yurie
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;
Great! The percentage is correct! Thank you so much for your help! Blessings! -Yurie
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.