Desktop productivity for business analysts and programmers

Count of a character column as subtotal

Reply
N/A
Posts: 0

Count of a character column as subtotal

I am trying to generate a detail report with subtotal displayed at the end of each group. The summary line should display the count of a character column. When I use PROC Report I get an error message that anaysis variable is not numeric. Is there a way to do this.

Thanks,
KM
SAS Super FREQ
Posts: 8,719

Re: Count of a character column as subtotal

Hi:
I'm not sure what you mean by "count as a subtotal"...for example, the output from this program is too lengthy to post, however, there are two ways to get a count of the number of regions -- #1 puts the count of regions as a separate LINE and #2 puts the count of regions on the same grand total line as the total SALES sum.

If what you mean is that you would want to see the count of regions (3) under the SALES sum as a separate subtotal line, then refer to the example "Adding Multiple Summary Rows" that starts on page 7 of this paper:
http://support.sas.com/rnd/papers/sgf07/sgf2007-report.pdf

cynthia
[pre]
ods listing close;
options nodate nonumber center;
ods html file='c:\temp\getcount.html' style=sasweb;
proc report data=sashelp.shoes nowd;
title '1) Use LINE statement';
where region in ('Asia', 'Canada', 'Pacific');
column region product sales;
define region / order;
define product / display;
define sales / sum;
compute before region;
holdreg +1;
endcomp;
rbreak after / summarize;
compute after;
line 'Number of Regions: ' holdreg comma6.;
endcomp;
run;

proc report data=sashelp.shoes nowd;
title '2) Put N in space for Region on Total Line';
where region in ('Asia', 'Canada', 'Pacific');
column region product sales;
define region / order;
define product / display;
define sales / sum;
compute before region;
holdreg +1;
endcomp;
rbreak after / summarize;
compute after;
tmpvar = catt('N =',put(holdreg, comma6.));
region = tmpvar;
endcomp;
run;

ods html close;
[/pre]
N/A
Posts: 0

Re: Count of a character column as subtotal

Thank You! Cynthia.
I want to display the count of nonmissing character/date field in the subtotal line. The count in your code would give me the count of all observations.
Is there a way to count only a column's nonmissing values.
This is the output I am trying to generate.

COLA COLB COLC
122 10/1/2007 A
122 . A
122 10/4/2007 A
Count = 2
123 10/3/2007 A
123 10/3/2007 A
Count=4
124 10/3/2007 A
124 . A
Count=1

Thanks,
KM
SAS Super FREQ
Posts: 8,719

Re: Count of a character column as subtotal

Hi:
The examples only give the sum of the total rows because I wrote out the information in a compute AFTER block. You can also "break" on group or order variables, so the same technique can be used to put a total at the end of each group -- for example at the end of each unique value for the ColA var.

There are a few other things that need to be taken care of...since ColB is a date variable, we need to format it as a date on the regular display rows but when we move the count of non missing values into the summary row for ColB, we also need to change the format to be a non-date format on the summary line -- this is what's being taken care of with the CALL DEFINE statement.

The N statistic does represent the count of non-missing values. As you can see on report #1 -- the N is correct for each value of ColA. Using NOPRINT allows us to let PROC REPORT compute the correct number and then we use the N report column value at the break for ColA. (Report #3 is a variation on #2...when ColA has an ORDER usage, the repetitious values of ColA are suppressed on the detail lines -- this is a PROC REPORT "feature"...if you don't like/want this feature, you have to make a computed column to display the value of ColA on every report row.)

cynthia
[pre]

data testdata;
length cola $10;
infile datalines;
input cola $ colb : mmddyy10. colc $;
return;
datalines;
122 10/1/2007 A
122 . A
122 10/4/2007 A
123 10/3/2007 A
123 10/4/2007 A
123 10/5/2007 A
123 10/6/2007 A
124 10/3/2007 A
124 . A
;
run;

ods html file='c:\temp\use_n.html' style=sasweb;
title '1) Use ColA for Ordering and show count in sep column';
proc report data=testdata nowd ;
column cola colb colb=cntdate colc;
define cola / order 'ColA';
define colb /sum f=mmddyy10. 'ColB';
define cntdate / n;
define colc / display 'ColC';
break after cola / summarize;
compute colb;
if upcase(_break_) = 'COLA' then
call define(_col_,'format','comma10.');
endcomp;
compute after cola;
COLA = 'Count =';
colb.sum = cntdate;
line ' ';
endcomp;
run;

title '2) Use ColA for Ordering and move count under date col';
proc report data=testdata nowd ;
column cola colb colb=cntdate colc;
define cola / order 'ColA';
define colb /sum f=mmddyy10. 'ColB';
define cntdate / n noprint;
define colc / display 'ColC';
break after cola / summarize;
compute colb;
if upcase(_break_) = 'COLA' then
call define(_col_,'format','comma10.');
endcomp;
compute after cola;
COLA = 'Count =';
colb.sum = cntdate;
line ' ';
endcomp;
run;

title '3) proc report show all values ColA';
proc report data=testdata nowd ;
column cola disp_cola colb colb=cntdate colc;
define cola / order noprint;
define disp_cola / computed 'ColA';
define colb /sum f=mmddyy10. 'ColB';
define cntdate / n noprint;
define colc / display 'ColC';
break after cola / summarize;
compute before cola;
holdcola = cola;
endcomp;
compute disp_cola / character length=12;
if upcase(_break_) = 'COLA' then
disp_cola = catt(holdcola, ' Count =');
else disp_cola = holdcola;
endcomp;
compute colb;
if upcase(_break_) = 'COLA' then
call define(_col_,'format','comma10.');
endcomp;
compute after cola;
colb.sum = cntdate;
line ' ';
endcomp;
run;
ods html close;
[/pre]
N/A
Posts: 0

Re: Count of a character column as subtotal

Thanks! Cynthia
This worked.
Can I do this using Proc Print.
I want to use Proc Print so that I can get dynamic excel sheets names for separate pages.

KM
SAS Super FREQ
Posts: 8,719

Re: Count of a character column as subtotal

Hi:
Sadly, Proc PRINT does not support COMPUTE blocks or computed report items. PROC PRINT does support asking for the N -- but it is the number of obs -- either for the whole file or the whole BY group. Only PROC REPORT or PROC TABULATE allow you to ask for the N based on a variable and not on the observations for a group. (Also TABULATE does not have the COMPUTE block.) Only Proc REPORT has the NOPRINT option which allows you to "hide" a report item or a computed item. Only Proc REPORT has a LINE statement. ... etc, etc

However, if you want every ColA "group" to become a separate worksheet, then within a code node, you could generate an Excel workbook with 3 worksheets for this data -- SAS and ODS allow the creation of Spreadsheet Markup Language XML files (this is the Microsoft "flavor" of XML that describes an Excel 2002/2003 workbook).

The only hitch is that after EG creates the file, you have to open the file from a File--Open in Excel or you have to launch it from Windows Explorer. Right now, EG does not like to open the kind of Spreadsheet Markup Language XML that TAGSETS.EXCELXP creates.

cynthia

[pre]
data testdata;
length cola $10;
infile datalines;
input cola $ colb : mmddyy10. colc $;
return;
datalines;
122 10/1/2007 A
122 . A
122 10/4/2007 A
123 10/3/2007 A
123 10/4/2007 A
123 10/5/2007 A
123 10/6/2007 A
124 10/3/2007 A
124 . A
;
run;

ods listing;
ods tagsets.excelxp file='c:\temp\mult.xls' style=sasweb;

title '2) Use ColA for Ordering and move count under date col';
proc report data=testdata nowd ;
column cola colb colb=cntdate colc;
define cola / order 'ColA';
define colb /sum f=mmddyy10. 'ColB';
define cntdate / n noprint;
define colc / display 'ColC';
break after cola / summarize page;
compute colb;
if upcase(_break_) = 'COLA' then
call define(_col_,'format','comma10.');
endcomp;
compute after cola;
COLA = 'Count =';
colb.sum = cntdate;
line ' ';
endcomp;
run;
ods _all_ close;
[/pre]
Ask a Question
Discussion stats
  • 5 replies
  • 94 views
  • 0 likes
  • 2 in conversation