topic Re: Proc report in SAS Programming

Proc report

Ronein — Thu, 11 Jun 2020 10:33:04 GMT

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;

Re: Proc report

ChrisNZ — Thu, 11 Jun 2020 22:28:25 GMT

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]

Re: Proc report

Ronein — Thu, 11 Jun 2020 11:23:15 GMT

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???

Re: Proc report

Ronein — Thu, 11 Jun 2020 11:25:27 GMT

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;

Re: Proc report

PaigeMiller — Thu, 11 Jun 2020 11:31:09 GMT

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.

Re: Proc report

smantha — Thu, 11 Jun 2020 11:44:19 GMT

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

Re: Proc report

ChrisNZ — Thu, 11 Jun 2020 11:46:33 GMT

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.

Re: Proc report

MCoopmans — Thu, 11 Jun 2020 11:47:37 GMT

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;

Re: Proc report

Ronein — Fri, 12 Jun 2020 03:56:52 GMT

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 ,

Re: Proc report

Ronein — Fri, 12 Jun 2020 03:57:32 GMT

May you show the solution with other procedures please?

Re: Proc report

Ronein — Fri, 12 Jun 2020 04:10:06 GMT

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?

Re: Proc report

PaigeMiller — Fri, 12 Jun 2020 13:29:20 GMT

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.

Re: Proc report

Cynthia_sas — Fri, 12 Jun 2020 14:25:35 GMT

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