topic Re: How to take count against any variable in sas proc report in SAS Programming

How to take count against any variable in sas proc report

Shantaram — Thu, 17 Sep 2020 04:28:08 GMT

input table

Date policy_no
1-Apr-20 53398314
1-Apr-20 53424502
1-Apr-20 53432609
1-May-20 53435311
1-Apr-20 53435321
1-Aug-20 53435707
1-Apr-20 53437505
1-Apr-20 53438590
1-Apr-20 53441689
1-Apr-20 53444668
1-Apr-20 53445856
1-May-20 53494214
1-May-20 53494233
1-May-20 53494334
1-May-20 53494347
1-May-20 53494359
1-May-20 53494399
1-May-20 53494445
1-May-20 53494571

Required Output

Date	Count of policy_no
1-Apr-20	9
1-May-20	9
1-Aug-20	1
Grand Total	19

i want count of policies against date variable in sas proc report.

Thanks in advance for your replay.

Re: How to take count against any variable in sas proc report

Shmuel — Thu, 17 Sep 2020 05:00:45 GMT

You can use next code though it's not by proc report:

data have;
 input date date9. policy_no ;
 format date date9.;
cards;
1-Apr-20 53398314
1-Apr-20 53424502
1-Apr-20 53432609
1-May-20 53435311
1-Apr-20 53435321
1-Aug-20 53435707
1-Apr-20 53437505
1-Apr-20 53438590
1-Apr-20 53441689
1-Apr-20 53444668
1-Apr-20 53445856
1-May-20 53494214
1-May-20 53494233
1-May-20 53494334
1-May-20 53494347
1-May-20 53494359
1-May-20 53494399
1-May-20 53494445
1-May-20 53494571
; run;

proc sort data=have out=sorted nodupkey;
  by date policy_no;
 run;

data to_rep;
 set sorted;
  by date;
     retain count;
     *format date date9.;
     if first.date then count=1; else count+1;
     if last.date then output;
     drop policy_no;
run;

Re: How to take count against any variable in sas proc report

Shantaram — Thu, 17 Sep 2020 05:06:37 GMT

Thanks Shmuel,

I want same solution in proc report.

Re: How to take count against any variable in sas proc report

Shantaram — Thu, 17 Sep 2020 05:20:19 GMT

proc report data=Final
STYLE(LINES)=[FONT=('TIMES NEW ROMAN',5PT) JUST=LEFT color=BLACK]
STYLE(REPORT)=[FONT=('TIMES NEW ROMAN',6PT)]
STYLE(HEADER)=[FONT=('Arial', 9PT, Bold) background=CX13478C fontsize=1.5 color=white]
STYLE(COLUMN)=[FONT=('Arial',8PT) fontsize=1.5 COLOR=BLACK ];
where pilot='Control';
column date
Policy_no

;

define date/ group 'Due_Month';
define policy_no/NOPRINT;

define Cont_Policies/computed width=3 '# Policies' style=[tagattr="format:#,##0.00%"];
compute Cont_Policies;
/*if pilot='Control' then */
Cont_Policies= N(Policy_no);

endcomp;

run;

Re: How to take count against any variable in sas proc report

Shmuel — Thu, 17 Sep 2020 08:15:06 GMT

I have searched sas documentation for statistics available in proc report.

For my best understanding the only statistic built in is SUM:

statistic
associates a statistic with an analysis variable. You must associate a statistic with every analysis variable in its definition. PROC REPORT uses the statistic that you specify to calculate values for the analysis variable for the observations that are represented by each cell of the report. You cannot use statistic in the definition of any other type of variable.
See Statistics That Are Available in PROC REPORT for a list of available statistics.

Default	SUM
Note	PROC REPORT uses the name of the analysis variable as the default heading for the column. You can customize the column heading with the column-header option in the DEFINE statement.
Examples	Ordering the Rows in a Report
Using Aliases to Obtain Multiple Statistics for the Same Variable

Consolidating Multiple Observations into One Row of a Report

any other should be computed.

Next are 3 methods to get the wanted results, (1) by proc freq (2) by a data step (3) by proc report.

data have;
 input date date9. policy_no ;
 format date date9.;
cards;
1-Apr-20 53398314
1-Apr-20 53424502
1-Apr-20 53432609
1-May-20 53435311
1-Apr-20 53435321
1-Aug-20 53435707
1-Apr-20 53437505
1-Apr-20 53438590
1-Apr-20 53441689
1-Apr-20 53444668
1-Apr-20 53445856
1-May-20 53494214
1-May-20 53494233
1-May-20 53494334
1-May-20 53494347
1-May-20 53494359
1-May-20 53494399
1-May-20 53494445
1-May-20 53494571
; run;

proc sort data=have out=sorted nodupkey;
  by date policy_no;
 run;
 
/* SOLUTION 1 by proc freq */
proc freq data=sorted;
  table date / out=freq(keep=date count)  
               nocol norow nocum nopercent; 
run; 
/* SOLUTION 2 by a data step */
data to_rep;
 set sorted;
  by date;
     retain count;
     
     if first.date then count=1; else count+1;
     if last.date then output;
     drop policy_no;
run;

/* SOLUTION 3 by a proc freq */
proc sort data=have;
  by date policy_no;
 run;
data sorted;
 set have;
  by date policy_no;
     if first.policy_no then Count_Policies=1;
     else Count_Policies=0;
run;

proc report data=sorted;
	column date Count_Policies /* N=Count_Policies*/;
	define date / display group 'Due_Month';
	define Count_Policies / analysis sum '# Policies';
run;

Re: How to take count against any variable in sas proc report

andreas_lds — Thu, 17 Sep 2020 08:27:11 GMT

Please try:

proc report data=work.have;
   define date / group order=data;
   define policy_no / n;
   
   rbreak after / summarize;
run;

Re: How to take count against any variable in sas proc report

ballardw — Thu, 17 Sep 2020 15:06:11 GMT

This may get you started

proc report data=have;
   columns date date=count;
   define date/group;
   define count /n format=best5. "Count of policy_no";
   rbreak after/summarize;
run;