topic Re: I need sugesstions about how can I create a report like this in ODS and Base Reporting

I need sugesstions about how can I create a report like this

mona4u — Tue, 20 Feb 2018 14:36:10 GMT

I'm using proc report and this is the code that I'm using

proc report data=auto3; 
 column Sites_Contracted site_number1 Site_Approved1 Date_Supplied3;
 define Sites_Contracted / analysis mean "Sites Contracted"; 
 define site_number1 / analysis n "Sites Initiated"; 
 define Site_Approved1 /  sum "Sites Qualified";
 define Date_Supplied3/ sum "Sites Supplied"  ;

 run;

and this is my current results

Sites Contracted Sites Initiated Sites Qualified Sites Supplied

but I need to know how I can get the second row as it showing below (my aimed result) by knowing that I got the percentages by using this formula

site contracted %=(site contacted/site contracted )*100

site initiated %= (site initiated/site contracted)*100

site qulalified%=(site qualified/site contracted)*100

site supplied %= (site supplied/site contracted)*100

Sites Contracted	Sites Initiated	Sites Qualified	Sites Supplied
34	37	6	7
100%	108.80%	17.60%	20.60%

this is my input

Obs	Sites_Contracted	site_number1	Site_Approved1	Date_Supplied3
1	34	1000	.	.
2	34	1001	.	.
3	34	1050	1	.
4	34	1100	.	1
5	34	1200	1	1
6	34	1250	1	1
7	34	1252	.	.
8	34	1300	.	.
9	34	1350	.	1
10	34	1351	.	.
11	34	1400	.	.
12	34	1401	.	.
13	34	1450	.	.
14	34	1451	1	.
15	34	1452	.	.
16	34	1453	.	.
17	34	1500	.	.
18	34	1600	1	.
19	34	1601	1	1
20	34	1602	.	.
21	34	1700	.	1
22	34	1750	.	.
23	34	5000	.	.
24	34	5002	.	.
25	34	5003	.	.
26	34	5003	.	.
27	34	5003	.	.
28	34	5003	.	.
29	34	5003	.	.
30	34	5003	.	.
31	34	5003	.	.
32	34	5004	.	.
33	34	5005	.	.
34	34	5006	.	.
35	34	5007	.	.
36	34	5008	.	1
37	34	5009	.	.

Re: I need sugesstions about how can I create a report like this

Cynthia_sas — Tue, 20 Feb 2018 03:16:45 GMT

Hi:
Whether you can do this with PROC REPORT depends on the structure of your INPUT data (in WORK.AUTO3. Since you didn't post any data, people would need to make some fake data to even attempt a suggestion.

What does your INPUT data look like? What destination are you trying to create for this report: HTML, RTF, PDF, or ???? You have a couple of choices but they really depend on the structure of the input data.

cynthia

Re: I need sugesstions about how can I create a report like this

mona4u — Tue, 20 Feb 2018 14:35:09 GMT

Obs Sites_Contracted site_number1 Site_Approved1 Date_Supplied3
1 34 1000 . .
2 34 1001 . .
3 34 1050 1 .
4 34 1100 . 1
5 34 1200 1 1
6 34 1250 1 1
7 34 1252 . .
8 34 1300 . .
9 34 1350 . 1
10 34 1351 . .
11 34 1400 . .
12 34 1401 . .
13 34 1450 . .
14 34 1451 1 .
15 34 1452 . .
16 34 1453 . .
17 34 1500 . .
18 34 1600 1 .
19 34 1601 1 1
20 34 1602 . .
21 34 1700 . 1
22 34 1750 . .
23 34 5000 . .
24 34 5002 . .
25 34 5003 . .
26 34 5003 . .
27 34 5003 . .
28 34 5003 . .
29 34 5003 . .
30 34 5003 . .
31 34 5003 . .
32 34 5004 . .
33 34 5005 . .
34 34 5006 . .
35 34 5007 . .
36 34 5008 . 1
37 34 5009 . .

Re: I need sugesstions about how can I create a report like this

Cynthia_sas — Tue, 20 Feb 2018 14:48:56 GMT

Hi:

Your data looks odd. Date_Supplied3 just seems to be a missing or 1 -- which in SAS dates would be Jan 2, 1960 as a date. That seems odd Here's an example using SASHELP.SHOES, and I only got the sum statistic for the 3 variables I used.

The COMPUTE block is what will allow you to put a percent at the bottom of each column. But first you need an RBREAK statement, as shown in #1 report to get the second row on the report.

Next, in the COMPUTE block, the first thing to do is grab and save the SALES.SUM value on the break to use it for the divisions. I made a variable called DIVIDE_BY. In my assignment statement I have DIVIDE_BY=SALES.SUM because when you have an analysis variable that you are using in a COMPUTE block, the correct way to refer to the variable is var_name.stat_name ...so I have the SUM statistic for SALES, INVENTORY and RETURNS, so the correct way to refer to them is SALES.SUM, INVENTORY.SUM and RETURNS.SUM. If I had asked for the MEAN statistic for SALES, for example, then the reference would be SALES.MEAN or if I had asked for the MAX statistic for SALES, then the reference would be SALES.MAX.

In the compute block, after I make DIVIDE_BY, I use it in 3 assignment statements, one where I change the displayed value for SALES.SUM, the displayed value for INVENTORY.SUM and the displayed value for RETURNS.SUM. You have a multiply by 100 in your formula, but the percent format will do that for you. In order to use a different format for the percent row, I needed a CALL DEFINE statement for each variable to change the format. The default format for these variables is a dollar. format, so the only way to change it for the last row is to use CALL DEFINE.

Hope this helps,

cynthia