The SAS Output Delivery System and reporting techniques

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

Accepted Solution Solved
Reply
Regular Contributor
Posts: 165
Accepted Solution

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

[ Edited ]

 

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

343767

 

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 ContractedSites InitiatedSites QualifiedSites Supplied
343767
100%108.80%17.60%20.60%

 

 

this is my input 

ObsSites_Contractedsite_number1Site_Approved1Date_Supplied3
1341000..
2341001..
33410501.
4341100.1
534120011
634125011
7341252..
8341300..
9341350.1
10341351..
11341400..
12341401..
13341450..
143414511.
15341452..
16341453..
17341500..
183416001.
1934160111
20341602..
21341700.1
22341750..
23345000..
24345002..
25345003..
26345003..
27345003..
28345003..
29345003..
30345003..
31345003..
32345004..
33345005..
34345006..
35345007..
36345008.1
37345009..

 


Accepted Solutions
Solution
‎02-20-2018 10:25 AM
SAS Super FREQ
Posts: 9,433

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

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.

two_row_report.png

 

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

View solution in original post


All Replies
SAS Super FREQ
Posts: 9,433

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

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
Regular Contributor
Posts: 165

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

Posted in reply to Cynthia_sas
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 . .
Solution
‎02-20-2018 10:25 AM
SAS Super FREQ
Posts: 9,433

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

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.

two_row_report.png

 

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 186 views
  • 1 like
  • 2 in conversation