topic How can I use proc report from multiple datasets to make a summary report? in ODS and Base Reporting

How can I use proc report from multiple datasets to make a summary report?

dory — Wed, 27 May 2015 03:53:56 GMT

Hi everyone,

I'm quite new to SAS and currently I'm trying to come up with a daily summary report that should look something like this:

		Remarks
Overall Stock (%)	90.34	+1.00% from yesterday
Amount of tuna (Mg)	4000.6	-
Total Revenue ($ '000)	30.1	-

I have the individual values (Overall Stock, Amount of tuna, Total Revenue) in different queries (1 row each, 2 variables for overall stock and 1 variable for the other two) but how can I go about consolidating them into a table like this?

I understand that it is possible to join the queries vertically but this does not allow me to overwrite the formatting depending on where the value came from.

Thanks in advance!

Re: How can I use proc report from multiple datasets to make a summary report?

Ksharp — Wed, 27 May 2015 12:04:02 GMT

PROC SQL + UNION

Re: How can I use proc report from multiple datasets to make a summary report?

RW9 — Wed, 27 May 2015 12:12:41 GMT

Its a common question this. If your output has difffering data and formats, then the easiest way to get it is to create a shell dataset where all the variables are text. Then you can put what you want in each cell, e.g. (and note I am just making up your data here, you will need to change it to fit what you have):

data template;

length col1 col2 col3 $200;

set overall_stock (in=a) amount_of_tuna (in=b) total_revenue (in=c);

if a then do;

col1="Overall Stock (%);

col2=strip(put(stock,8.2));

col3="+"||stript(put(pcent,8.2))||"% from yesterday";

output;

end;

else if b then do;

col1="Amount of tuna (Mg)";

col2=strip(put(tuna,8.1));

col3="-";

output;

end;

else do;

...

end;

run;

Re: How can I use proc report from multiple datasets to make a summary report?

Cynthia_sas — Wed, 27 May 2015 13:01:20 GMT

Hi:

PROC REPORT will allow you to use a different format on a different row, depending on the value of what is in the first column.

So if you do restructure your data so that it is approximately in this format, your 2nd column could be all numeric and then you could use a CALL DEFINE statement to change the format for report purposes. The code that produced this screen shot is below. I made a "helper" variable called ORD to make it easier to test which format to use for which row and also because it appears you want Overall Stock to appear before Amount of tuna on the report and normally, PROC REPORT would put the A before the O. So the ORD variable is helping to set the non-alpha order that you want for the rows.

Cynthia

data testdata;
length Type $20 val 8 remark $80;
infile datalines dlm=',' dsd;
input ord type $ val remark $;

** percent format will multiply by 100, so adjust number;
if ord = 1 then val = val/100;
return;
datalines4;
1,"Overall Stock (%)", 90.34," +1.00% from yesterday"
2,"Amount of tuna (Mg)", 4000.6," - "
3,"Total Revenue ($ '000)", 30.1," - "
;;;;
run;

ods html file='c:\temp\diff_format.html';

proc report data=testdata;
column ord type val remark;
define ord / order noprint;
define type / order order=data ' ';
define val / display ' ';
define remarks / display;
compute val;
    if ord = 1 then
      call define(_col_,'format','percent9.2');
    else if ord = 2 then
      call define(_col_,'format','best8.1');
    else if ord = 3 then
      call define(_col_,'format','dollar9.1');
endcomp;
run;
ods html close;

Re: How can I use proc report from multiple datasets to make a summary report?

dory — Thu, 28 May 2015 02:49:31 GMT

Hi xia keshan, RW9 and Cynthia,

Thanks so much for your responses. I have a lot more rows than the table I included above, so a shell dataset will be quite time consuming to write and may not be easily maintained by other operators in future.

I have managed to convert the formats, but only for the first column. I tried to use

call define(_C2_,'format','percent9.2');

but the column doesn't change its format. Any advice? 😕

Re: How can I use proc report from multiple datasets to make a summary report?

Cynthia_sas — Thu, 28 May 2015 18:39:40 GMT

Hi:

You don't show enough code or data to make a constructive comment. It will really depend on the OTHER statements in your program and the usages that you have in your DEFINE statements. You might want to work with Tech Support on this.

cynthia

Re: How can I use proc report from multiple datasets to make a summary report?

dory — Fri, 29 May 2015 01:25:08 GMT

Hi, this is what I have:

proc report data=qry_join

noheader

split='|';

column ID Descriptor Value ValueDiff;

define ID / noprint display order;

define descriptor / display;

define value / display center;

define valuediff / display center;

compute value;

if ID=1 OR ID=2 then

do;

call define(_COL_,'FORMAT','8.2');

call define(_C2_,'FORMAT','8.1');

end;

else do;

call define(_COL_,'FORMAT','10.1');

end;

endcomp;

run;

And when I run it, I get these errors/notes:

NOTE: Invalid use of _COL_ or _ROW_ in CALL DEFINE