The SAS Output Delivery System and reporting techniques

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

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

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

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!


Accepted Solutions
Solution
‎05-29-2015 12:37 PM
SAS Super FREQ
Posts: 8,646

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

Hi:

  Again, without data, it is hard to comment in great detail. It appears that the data used by this program is not the same as the data with REMARKS that you posted originally.

  But there are some obvious issues. You do not have any ACROSS statements. So not sure why you are referencing _C2_ -- that is generally reserved for working with ACROSS items. If you want to change the value for DESCRIPTOR (which, conceptually might be thought of a _C2_), then the CALL DEFINE would be:

call define ('descriptor','format', '8.1'); or

call define('_c2_','format','8.1')

So, the issue with either reference is that even if you did properly quote the first argument, DESCRIPTOR is probably a character variable and can't take a numeric format. Also, it is incorrect to have both display and order as the usage for your ID variable. If you look at my example, my ORD variable was defined ONLY as an ORDER item on the report.

IF, on the other hand, you are trying to change the VALUEDIFF item, then _C2_ is absolutely the wrong way to reference that column for 2 reasons. 1) PROC REPORT works from left to right and VALUEDIFF is placed AFTER VALUE in the column statement. So you can't touch VALUEDIFF in the COMPUTE block for VALUE and 2) VALUEDIFF is NOT _C2_ -- you would need a separate compute block for VALUEDIFF.

  Modifying my previously posted sample data to include VALUEDIFF, I produced these results with the code shown in the screen shot. Notice that there is no reference to _C2_ needed. I changed the style as well as the format, so you could see the specific statements and where the changes were made.

cynthia

compute_block.png

cynthia

View solution in original post


All Replies
Grand Advisor
Posts: 9,335

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

PROC SQL + UNION

Esteemed Advisor
Esteemed Advisor
Posts: 6,726

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

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;

SAS Super FREQ
Posts: 8,646

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

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.

proc_report_call_define.png

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;

New Contributor
Posts: 4

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

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

SAS Super FREQ
Posts: 8,646

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

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

New Contributor
Posts: 4

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

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

NOTE: Argument 1 to function DEFINE at line 3 column 7 is invalid.

ERROR: Invalid column specification in CALL DEFINE.

NOTE: Argument 1 to function DEFINE at line 3 column 7 is invalid.

NOTE: Invalid use of _COL_ or _ROW_ in CALL DEFINE

NOTE: Argument 1 to function DEFINE at line 3 column 7 is invalid.

NOTE: Invalid use of _COL_ or _ROW_ in CALL DEFINE

NOTE: Argument 1 to function DEFINE at line 3 column 7 is invalid.

ERROR: Invalid column specification in CALL DEFINE.

NOTE: Argument 1 to function DEFINE at line 3 column 7 is invalid.

NOTE: Invalid use of _COL_ or _ROW_ in CALL DEFINE

NOTE: Argument 1 to function DEFINE at line 3 column 7 is invalid.

Currently I have valuediff to show the +1.00% from yesterday instead of having the whole string.

Solution
‎05-29-2015 12:37 PM
SAS Super FREQ
Posts: 8,646

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

Hi:

  Again, without data, it is hard to comment in great detail. It appears that the data used by this program is not the same as the data with REMARKS that you posted originally.

  But there are some obvious issues. You do not have any ACROSS statements. So not sure why you are referencing _C2_ -- that is generally reserved for working with ACROSS items. If you want to change the value for DESCRIPTOR (which, conceptually might be thought of a _C2_), then the CALL DEFINE would be:

call define ('descriptor','format', '8.1'); or

call define('_c2_','format','8.1')

So, the issue with either reference is that even if you did properly quote the first argument, DESCRIPTOR is probably a character variable and can't take a numeric format. Also, it is incorrect to have both display and order as the usage for your ID variable. If you look at my example, my ORD variable was defined ONLY as an ORDER item on the report.

IF, on the other hand, you are trying to change the VALUEDIFF item, then _C2_ is absolutely the wrong way to reference that column for 2 reasons. 1) PROC REPORT works from left to right and VALUEDIFF is placed AFTER VALUE in the column statement. So you can't touch VALUEDIFF in the COMPUTE block for VALUE and 2) VALUEDIFF is NOT _C2_ -- you would need a separate compute block for VALUEDIFF.

  Modifying my previously posted sample data to include VALUEDIFF, I produced these results with the code shown in the screen shot. Notice that there is no reference to _C2_ needed. I changed the style as well as the format, so you could see the specific statements and where the changes were made.

cynthia

compute_block.png

cynthia

New Contributor
Posts: 4

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

Hi Cynthia,

Thanks for the detailed explanation. I didn't realise that _C2_ was restricted to ACROSS variables -- sorry, I'm quite new to SAS.

I managed to change the format for the second column doing the compute block like you suggested in your image.

Thank you!!

Post a Question
Discussion Stats
  • 8 replies
  • 1442 views
  • 0 likes
  • 4 in conversation