topic Proc report - creating variable number of line statements for a group? in ODS and Base Reporting

Proc report - creating variable number of line statements for a group?

FrankE — Mon, 27 Feb 2012 16:46:16 GMT

Hi guys, I come to you with another question that's got me stumped. I whipped up the example below based on the "sashelp.cars" dataset to keep things nice and simple.

Here's what I'm trying to do: After each group of car "make" I want to print a line showing all unique occurances of car "type" and "cylinders". Each group can have a different amount of combinations, which is where I get stuck. In the example below, the variable "num" should hold the number of unique occurances within a group but I can't figure out how to use that value in the do loop below it. Currently I have it hard coded to "2". Any help would be appreciated!! Thanks!

data test;

set sashelp.cars;

key = strip(type) || '_' || strip(cylinders);

keep make type cylinders key;

run;

%macro create_report;

ods tagsets.ExcelXP path="c:\temp" file="test.xls";

proc report data=test missing nowindows split='*';

column make type cylinders key;

define make / group;

define key / group;

define type / display;

define cylinders / display;

compute before make;

num = 0;

endcomp;

compute after key;

num = num + 1;

call symput("key" || strip(num), key);

call symput("num",strip(num)); /**<-- at the end of each group num should hold the number of unique combos **/

endcomp;

break after make / summarize;

compute after make;

%do i = 1 %to 2; /** <-----how do I use "num" instead of "2" ?? **/

type&i = scan(input(symget(%bquote('key&i')), $char50.),1,'_');

cylinders&i = scan(input(symget(%bquote('key&i')), $char50.),2,'_');

line @10 'Type: ' type&i $char50. @30 ' Cylinders: ' cylinders&i $char50.;

%end;

endcomp;

run;

ods _all_ close;

%mend create_report;

%create_report;

Proc report - creating variable number of line statements for a group?

Ksharp — Tue, 28 Feb 2012 08:17:34 GMT

What does your report look like ?

Proc report - creating variable number of line statements for a group?

christa — Tue, 28 Feb 2012 10:01:07 GMT

What about a simple proc tabulate?

data test;

set sashelp.cars;

number = 1;

keep make type cylinders key number;

run;

proc tabulate data=test;

var number;

class make type cylinders;

table make*type*cylinders, number;

run;

Proc report - creating variable number of line statements for a group?

Ksharp — Wed, 29 Feb 2012 08:38:04 GMT

I don't think you can dynamically add line statement in proc report.

But maybe you can use data step to make such a dataset, then proc print it.

Ksharp

Proc report - creating variable number of line statements for a group?

Cynthia_sas — Wed, 29 Feb 2012 14:48:09 GMT

I still don't have a good picture in my mind of what the output report should look like. The usage of GROUP should be generating messages in the LOG because the usage of TYPE and CYLINDERS is DISPLAY -- and without MODEL or PRICE or other info, the report would just look like a big long list of MAKES, TYPES and CYLINDERS with a bunch of info at the break. The OP has never said whether the TABULATE report is closer to what he wanted.

LINE statements can't be conditional, so that is an issue. The macro seems to be trying to generate a varying number of LINE statements, but I don't think the macro will work the way the OP thinks it's going to work.

cynthia

Proc report - creating variable number of line statements for a group?

FrankE — Wed, 29 Feb 2012 15:16:44 GMT

Hey guys thanks for your replys. I know this doesn't make much sense in this simplified version as the actual report is far more complex. I just stripped it down to the bare minimum to make the example easy to follow.

This is basically a 'report within a report'. The actual report shows a list of transactions and the transaction amount, grouped by store. Each transaction can have a type of discount applied to it. At then end of each group the user wants a list of the different types of discounts applied to transactions for that store and the total amount of discounts for each type. Something like:

Store Transaction Amount Discount Type Discount Amount

12345 100.00 4 10.00

12345 200.00 4 20.00

12345 100.00

12345 100.00 2 5.00

----------------------------------------------------------------------------------------------------------------

500.00 35.00

Discount Type: 4 Total Discount: 30.00

Discount Type: 2 Total Discount: 5.00

Proc report - creating variable number of line statements for a group?

Cynthia_sas — Thu, 01 Mar 2012 06:13:15 GMT

OK...I see what you want. I actually think you can do it without using any macro variables or macro routines at all. I am not on a computer where I have SAS right now, but I think this is possible using a combination of a temporary variable and making a big string with your own line feed/new line ODS ESCAPECHAR (or since you are using TAGSETS.EXCELXP, using the command for inserting a line feed (Alt+Enter) into a cell).

LINE statements are ALWAYS executed after all the other statements in a COMPUTE block. So they cannot be executed conditionally. You can alter the text of a LINE statement and you can alter the format (or cause a LINE not to print by setting the length to 0), but you cannot conditionally execute a LINE statement directly. See these notes for some ideas:

http://support.sas.com/kb/37/763.html

http://support.sas.com/kb/24/323.html

cynthia

Re: Proc report - creating variable number of line statements for a group?

FrankE — Thu, 01 Mar 2012 13:52:16 GMT

I considered that approach but was trying to avoid that because the larger report can be outputted in XLS, HTML, and PDF.. and the linebreak charactes are different for all of those. I guess thats the only way though.. thanks for your help!

Re: Proc report - creating variable number of line statements for a group?

Cynthia_sas — Thu, 01 Mar 2012 14:21:36 GMT

When I run this in SAS 9.3, using ^ as the ESCAPECHAR+{newline 1} gives me line breaks in all destinations.

cynthia

data tryit;
length bigline $400.;
bigline = catx('^{newline 1}',
                 'Twas brillig and the slithy toves',
                 'Did gyre and gimble in the wabe',
                 'All mimsy were the borogroves',
                 'And the mome raths outgrabe.',
                 '^{newline 1}',
                 'Beware the Jabberwock, my son',
                 'The jaws that bite, the claws that snatch',
                 'Beware the Jubjub bird and shun',
                 'The frumious Bandersnatch');
run;

ods escapechar='^';
title; footnote;

ods msoffice2k file='c:\temp\test.html' style=sasweb;
ods tagsets.ExcelXP path="c:\temp" (url=none)
    file="test.xml" style=sasweb;
ods pdf file='c:\temp\test.pdf';
ods rtf file='c:\temp\test.rtf';

   proc report data=tryit nowd;
     column bigline;
     define bigline / display 'The Jabberwock by Lewis Carroll';
   run;
ods _all_ close;