Create summary report

[Ronein]

Data Tbl_1809;
input ID Team Y month;
cards;
1 817 10 1809
2 817 20 1809
3 818 30 1809
4 828 40 1809
5 828 50 1809
6 828 60 1809
7 811 70 1809
8 810 80 1809
9 810 90 1809
10 809 100 1809
;
run;

Data Tbl_1712;
input ID Team Y month;
cards;
2 817 25 1712
3 818 35 1712
4 828 45 1712
5 828 56 1712
6 828 65 1712
7 811 75 1712
11 817 85 1712
12 817 95 1712
13 818 100 1712
;
run;

Data RawData;
Set Tbl_1809 Tbl_1712;
Run;
 

Hello

I want to create a summary report that will contain following 11 columns( please see the photo)

Raw data contain following 3 columns;

Id 

Team

Y

[error_prone]

I don't see any picture, just an excel-file, but hlrhis could be caused by the mobile version of the communities.

[Ronein]

You can the the desired report in the excel file that was attached

[RW9]

And what have you done towards getting your desired output?  A simple proc freq of your data, then transpose the results seems to be all that is necessary.

[Ronein]

I did it with proc sql (several steps) and I want to learn other ways that are less code (more quickly)

for example:

proc report

proc tabulate

or other ways to get the desired output

[RW9]

proc report:

http://support.sas.com/documentation/cdl/en/proc/65145/HTML/default/viewer.htm#n1dz7jdasx5t56n1rmlx3...

examples:

https://www.google.com/search?q=sas+proc+report+examples&rlz=1C1GGRV_enGB751GB751&oq=sas+proc+report...

proc tabulate:

http://support.sas.com/documentation/cdl/en/proc/65145/HTML/default/viewer.htm#n1hpbwr9acrbmen1e2d6h...

examples:

https://www.google.com/search?rlz=1C1GGRV_enGB751GB751&ei=jr4YXKKHO_KT1fAPuYGhuAM&q=sas+proc+tabulat...

Not to mention the search bar on this forum.

[Ronein]

May you please send a code that create this report?

As you see we need to calculate sum per specific categories and it is not usual case

Joe Dave

[ballardw]

Your spreadsheet shows a column "Change". How is that actually related to your data? Change tends to imply some direction so is that from 1712 to 1809 or the other way around? Is the change something like total customers, average customers, max customers or something else? By the way "Customers" is not defined in your dat either. So where do we get customer values?

You should have supplied the values using your example data so can have a chance of determining where the undefined values are coming from. Since you used a spreadsheet it would not have been hard to type in formulae in the cells.

[Ronein]

Please find the code that creates the desired report.

As you can see it is a very long code and the target is to find a  better code(short).

Difference is value in 1809 minus value in 1712.

Number of customers is number of rows.(Each customer has 1 row in each month table)

Data Tbl_1809;
input ID Team Y month;
cards;
1 817 10 1809
2 817 20 1809
3 818 30 1809
4 828 40 1809
5 828 50 1809
6 828 60 1809
7 811 70 1809
8 810 80 1809
9 810 90 1809
10 809 100 1809
;
run;
Data Tbl_1712;
input ID Team Y month;
cards;
2 817 25 1712
3 818 35 1712
4 828 45 1712
5 828 56 1712
6 828 65 1712
7 811 75 1712
11 817 85 1712
12 817 95 1712
13 818 100 1712
;
run;
 

PROC SQL;
	create table P_817_1809 as
	select '817' as type,
	        sum(case when  Team=817 then 1 else 0 end )as No_Customers1809,
	        sum(case when  Team=817 then Y else 0 end ) as Y1809
from Tbl_1809
where Team=817
;
QUIT;
PROC SQL;
	create table P_817_1712 as
	select 	'817' as type,
	        sum(case when  Team=817 then 1 else 0 end )as No_Customers1712,
	        sum(case when  Team=817 then Y else 0 end ) as Y1712
from Tbl_1712
where Team=817
;
QUIT;



PROC SQL;
	create table P_818_1809 as
	select 	'818' as type,
	        sum(case when  Team=818 then 1 else 0 end )as No_Customers1809,
	        sum(case when  Team=818 then Y else 0 end ) as Y1809
from Tbl_1809
where Team=818
;
QUIT;
PROC SQL;
	create table P_818_1712 as
	select 	'818' as type,
	        sum(case when  Team=818 then 1 else 0 end )as No_Customers1712,
	        sum(case when  Team=818 then Y else 0 end ) as Y1712
from Tbl_1712
where Team=818
;
QUIT;





PROC SQL;
	create table P_828_1809 as
	select 	'828' as type,
	        sum(case when  Team=828 then 1 else 0 end )as No_Customers1809,
	        sum(case when  Team=828 then Y else 0 end ) as Y1809
from Tbl_1809
where Team=828
;
QUIT;
PROC SQL;
	create table P_828_1712 as
	select 	'828' as type,
	        sum(case when  Team=828 then 1 else 0 end )as No_Customers1712,
	        sum(case when  Team=828 then Y else 0 end ) as Y1712
from Tbl_1712
where Team=828
;
QUIT;



PROC SQL;
	create table P_817_818_828_1712 as
	select 	'817_818_828' as type,
	        sum(case when  Team in(817,818,828) then 1 else 0 end )as No_Customers1712,
	        sum(case when  Team in(817,818,828) then Y else 0 end ) as Y1712
from Tbl_1712
where Team in(817,818,828)
;
QUIT;
PROC SQL;
	create table P_817_818_828_1809 as
	select 	'817_818_828' as type,
	        sum(case when  Team in(817,818,828) then 1 else 0 end )as No_Customers1809,
	        sum(case when  Team in(817,818,828) then Y else 0 end ) as Y1809
from Tbl_1809
where Team in(817,818,828)
;
QUIT;





PROC SQL;
	create table P_All_1809 as
	select 	'All' as type,
	         count(*) as No_Customers1809,
	        sum(Y) as Y1809
from Tbl_1809
;
QUIT;
PROC SQL;
	create table P_All_1712 as
	select 	'All' as type,
	         count(*) as No_Customers1712,
	        sum(Y) as Y1712
from Tbl_1809
;
QUIT;



Data P_817;
length Type $20;
Merge P_817:;
run;
Data P_818;
length Type $20;
Merge P_818:;
run;
Data P_828;
length Type $20;
Merge P_828:;
run;
DATA P_817_818_828;
length Type $20;
Merge P_817_818_828_:;
run;
Data P_All;
Merge P_All:;
Run;



Data output;
SET P_817  P_818  P_828 P_817_818_828   P_All;
D_Y=Y1809-Y1712;
D_Customers=No_Customers1809-No_Customers1712;
PCT_Change_Y=(Y1809-Y1712)/Y1712;
PCT_Change_Customers=(No_Customers1809-No_Customers1712)/No_Customers1712;
format PCT_Change_Y   PCT_Change_Customers  percent9.2;
Run;

[RW9]

Well straight off that code has errors.  This for example:

case when  Team=817 then Y else 0 end

Y and 0 are not the same.  Also, please review the code that you have been presented in other posts, and the advice given on how to format code, such as indenting, not coding all in upper case, not splitting out same data into different datasets.  You have a lot of code there specifically because you have ignored the "don't split like data up into different datasets".

Now with your report, this will get you close, you can do the percent bit in a datastep and orgainse how you want.  Note this uses a multilabel format, which is shortcut trick to means/summary and other procedures over groups of data which can overlap.  You can find some information here:

https://blogs.sas.com/content/sgf/2016/12/16/creating-and-using-multilabel-formats/

Now for the code:

proc format;
  value team (multilabel)
    817="817"
    818="818"
    828="828"
    817,818,828="TOTAL_817_818_828"
    low-816,819-827,829-high="OTHER"
    low-high="ALL";
  value mnth (multilabel)
    1809="1809"
    1712="1702"
    low-high="Total";
run;

data tbl;
  input id team y month;
  format team team. month mnth.; 
cards;
1 817 10 1809
2 817 20 1809
3 818 30 1809
4 828 40 1809
5 828 50 1809
6 828 60 1809
7 811 70 1809
8 810 80 1809
9 810 90 1809
10 809 100 1809
2 817 25 1712
3 818 35 1712
4 828 45 1712
5 828 56 1712
6 828 65 1712
7 811 75 1712
11 817 85 1712
12 817 95 1712
13 818 100 1712
;
run;

proc means data=tbl;
  class month team / mlf;
  var y;
  output out=tbl2 n=n sum=sum;
run;

proc sort data=tbl2;
  by team month;
run;

proc transpose data=tbl2 out=i1 prefix=n;
  by team;
  var n;
  id month;
  where month ne "" and team ne "";
run;

proc transpose data=tbl2 out=i2 prefix=sum;
  by team;
  var sum;
  id month;
  where month ne "" and team ne "";
run;

data want;
  merge i1 i2;
  by team;
run;

You will note how all the same data is in one dataset and I use by grouping to process it, this is a fundamental part of Base SAS.