Proc report

[Ronein]

Hello

What  is the way to create the following summary report using Proc Report?

Thanks

proc format;
value FFF
1='ACCEPT'
2='Reject'
3='REFER'
;
run;

Data ttt;
format opendate  date9. Ind FFF.;
input ID Ind opendate :date9.;
cards;
1 1 07JUN2020'd
2 1 07JUN2020'd
3 1 07JUN2020'd
4 2 07JUN2020'd
5 1 07JUN2020'd
6 3 12JUN2020'd
7 3 12JUN2020'd
8 2 12JUN2020'd
9 2 12JUN2020'd
10 2 12JUN2020'd
;
run;
title 'Summary table';
PROC SQL;
	select opendate,
	       count(*) as No_customers,
	       sum(case when put(Ind,FFF.)='ACCEPT' then 1 else 0 end ) as no_customers_DECLINE,
	       sum(case when put(Ind,FFF.)='Reject' then 1 else 0 end ) as no_customers_ACCEPT,
	       sum(case when put(Ind,FFF.)='REFER' then 1 else 0 end ) as no_customers_REFER ,
           calculated  no_customers_DECLINE/calculated No_customers as pct_DECLINE format=percent8.2,
           calculated  no_customers_ACCEPT/calculated No_customers as pct_ACCEPT format=percent8.2,
           calculated  no_customers_REFER/calculated No_customers as pct_REFER format=percent8.2
	from  ttt
	group by opendate
;
QUIT;

[ChrisNZ]

1. Why proc report for this?

2. Note that 

sum(case when put(Ind,FFF.)='DECLINE' then 1 else 0 end ) as no_customers_DECLINE,

can be shortened to 

sum ( put(Ind,FFF.)='DECLINE' ) as no_customers_DECLINE,

3.  For clarity, No_customers  should probably be called Nb_customers instead.

No  is normally used for the identifier (number of the customer). This is called an ordinal number

Nb  is normally used for the count (number of customers). This is called a cardinal number

Since both of these are called a number in English, it is easy to confuse them. 

[Edited for typos. Sorry about that]

[PaigeMiller]

Note that

sum(case when put(Ind,FFF.)='ACCEPT' then 1 else 0 end ) as no_customers_DECLINE,

can be shortened to 

sum(case when ind=1 then 1 else 0 end ) as no_customers_ACCEPT,

I think you have just computed the number of customers that ACCEPT and not the number of customers that DECLINE, you seem to have ACCEPT and DECLINE reversed. Your formats are not providing value here, anyway.

[Ronein]

Thank you.

I agree that better name is Nb_customers (for number of customers).

I want to learn proc report and my challenge now to know how to do this report via proc report.

Let's ask on the other way: Why not to learn  do it via proc report??? 

[Ronein]

proc format;
value FFF
1='ACCEPT'
2='Reject'
3='REFER'
;
run;

Data ttt;
format opendate  date9. Ind FFF.;
input ID Ind opendate :date9.;
cards;
1 1 07JUN2020'd
2 1 07JUN2020'd
3 1 07JUN2020'd
4 2 07JUN2020'd
5 1 07JUN2020'd
6 3 12JUN2020'd
7 3 12JUN2020'd
8 2 12JUN2020'd
9 2 12JUN2020'd
10 2 12JUN2020'd
;
run;
title 'Summary table';
PROC SQL;
	select opendate,
	       count(*) as Nr_customers,
	       sum(case when put(Ind,FFF.)='ACCEPT' then 1 else 0 end ) as Nr_customers_DECLINE,
	       sum(case when put(Ind,FFF.)='Reject' then 1 else 0 end ) as Nr_customers_ACCEPT,
	       sum(case when put(Ind,FFF.)='REFER' then 1 else 0 end ) as Nr_customers_REFER ,
           calculated  Nr_customers_DECLINE/calculated Nr_customers as pct_DECLINE format=percent8.2,
           calculated  Nr_customers_ACCEPT/calculated Nr_customers as pct_ACCEPT format=percent8.2,
           calculated  Nr_customers_REFER/calculated Nr_customers as pct_REFER format=percent8.2
	from  ttt
	group by opendate
;
QUIT;
 

[smantha]

First of all I do not see the point of format created if it is not used for anything.

The case when can be changed to the following

sum((ind=1*1) ) as Nr_customers_DECLINE,
 sum((ind=2)*1 ) as Nr_customers_ACCEPT,
sum((ind=3)*1 ) as Nr_customers_REFER ,

for proc report consider this article https://support.sas.com/resources/papers/proceedings/proceedings/sugi30/259-30.pdf

[Ronein]

Thanks!

What is the reason that you multiply by 1?

Why will it no work like that please?

sum((ind=1)) as Nr_customers_DECLINE,
sum((ind=2)) as Nr_customers_ACCEPT,
sum((ind=3)) as Nr_customers_REFER ,

[ChrisNZ]

Edited my previous reply, sorry for the typo.

SQL is a clearer and easier to maintain in this case, but to learn, why not other procedures indeed.

[MCoopmans]

This would probably do the trick:

PROC REPORT DATA=WORK.TTT LS=93  PS=80  SPLIT="/" CENTER nowd;
COLUMNS  opendate ID Ind  pct_acc pct_ref pct_rej;

DEFINE  opendate / GROUP FORMAT= DATE9. WIDTH=9     SPACING=2   RIGHT "opendate" ;
DEFINE  Ind / ACROSS FORMAT= FFF6. WIDTH=6     SPACING=2   RIGHT "Ind" ;
DEFINE  ID / N FORMAT= BEST9. WIDTH=9     SPACING=2   RIGHT "Nr of Customers" ;

DEFINE pct_acc / computed "PCT_Accept" F=PERCENT8.2;
DEFINE pct_ref / computed "PCT_Reference" F=PERCENT8.2;
DEFINE pct_rej / computed "PCT_Rejected" F=PERCENT8.2;

COMPUTE pct_acc ;
 pct_acc=_c3_/ID.n;
ENDCOMP;

COMPUTE pct_ref ;
 pct_ref=_c4_/ID.n;
ENDCOMP;

COMPUTE pct_rej ;
 pct_rej=_c5_/ID.n;
ENDCOMP;


RUN;

[Ronein]

Thank you so much!!!
May I ask please how do you know that:
Number of customers accepts is in _C_3
Number of customers referred is in _C_4
Number of customers rejected is in _C_5

This is a big potential for doing mistake in this step.
I want to learn your way to find that accept is _c_3 ,refer is _c_4 and reject is _c5_ .

Why the order is like that?
Is it by alphanumeric order?

[PaigeMiller]

It is in the order of the columns specified in the PROC REPORT COLUMNS statement. Since variable IND is specified as an ACROSS variable, and it has three levels, the columns are _C3_ (which corresponds to ID=1 which is "accept") _C4_ (which corresponds to ID=2 which is "reject" _C5_(which corresponds to ID=3 which is "refer") so the levels are in numerical order, which is the default because ID is a numeric variable (if you do this for a character variable, the default ordering is alphabetical order)

Also note OPENDATE is _C1_ and ID is _C2_ according to the COLUMNS statement.

[Ronein]

May you show the solution with other procedures please?

[Cynthia_sas]

Hi:

  I don't understand this logic:

Is this a typo or is there some other reason that the Accepts are being counted as Declines?

And if you want them to appear as percent "decline" why does the format use the word Reject? What is the reason for the ACCEPT and REFER to be uppercase and the Reject  to be mixed case?

  PROC REPORT can do this. You'd have to use ACROSS items and getting the order you want might require some fiddling. Not sure about why you want the order you want. If the IND values are 1,2,3 (Accept,Reject,Refer), why display them as Decline, Accept, Refer???

Cynthia