topic Proc Report - Help needed in SAS Procedures

Proc Report - Help needed

sas_raj — Wed, 13 Mar 2013 11:37:37 GMT

My data consists of financial transactions, and I need to create a report with detailed records as well as customized summary (SAS 9.1.3 on Unix).

Simplified version of Data looks like:

Portfolio, Expiration Date, Trade Date, Master Ticket Number, Ticket Number, Ticket Type, Transaction Type, Amount

ABCDE, 2/28/2013, 2/20/2013, 1000, 1000, Type1, Buy, $100,000.00

ABCDE, 2/28/2013, 2/21/2013, 1000, 1001, Type1, Buy, $50,000.00

ABCDE, 2/28/2013, 2/22/2013, 1000, 1002, Type1, Buy, $40,000.00

ABCDE, 2/28/2013, 2/22/2013, 1000, 1003, Type1, Transfer, $60,000.00

ABCDE, 2/28/2013, 2/23/2013, 1000, 1004, Type1, Sell, $100,000.00

DEF, 2/28/2013, 2/20/2013, 1005, 1005, Type2, Buy, $100,000.00

DEF, 2/28/2013, 2/22/2013, 1005, 1006, Type2, Sell, $100,000.00

XYZ, 2/28/2013, 2/18/2013, 1007, 1007, Type1, Buy, $100,000.00

XYZ, 2/28/2013, 2/22/2013, 1007, 1008, Type1, Buy, $100,000.00

XYZ, 2/28/2013, 2/10/2013, 1009, 1009, Type1, Buy, $100,000.00

XYZ, 2/28/2013, 2/22/2013, 1009, 1010, Type1, Sell, $60,000.00

Report should look like:

On Page 1

Portfolio: ABCDE

Expiration Date, Trade Date, Master Ticket Number, Ticket Number, Transaction Type, Amount

2/28/2013, 2/20/2013, 1000, 1000, Buy, $100,000.00

2/28/2013, 2/21/2013, 1000, 1001, Buy, $50,000.00

2/28/2013, 2/22/2013, 1000, 1002, Buy, $40,000.00

2/28/2013, 2/22/2013, 1000, 1003, Transfer, $60,000.00

2/28/2013, 2/23/2013, 1000, 1004, Sell, $100,000.00

Net Position: $90,000.00

On Page 2

Portfolio: DEF

Expiration Date, Trade Date, Master Ticket Number, Ticket Number, Transaction Type, Amount

2/28/2013, 2/20/2013, 1005, 1005, Buy, $100,000.00

2/28/2013, 2/22/2013, 1005, 1006, Sell, $100,000.00

On Page 3

Portfolio: XYZ

Expiration Date, Trade Date, Master Ticket Number, Ticket Number, Transaction Type, Amount

2/28/2013, 2/18/2013, 1007, 1007, Buy, $100,000.00

2/28/2013, 2/22/2013, 1007, 1008, Buy, $100,000.00

Net Position: $0

2/28/2013, 2/10/2013, 1009, 1009, Buy, $100,000.00

2/28/2013, 2/22/2013, 1009, 1010, Sell, $60,000.00

Net Position: $40,000.00

So, the requirements are:

1. Whenever Portfolio changes, a new page should begin. One portfolio can span over multiple pages, and if it does, the page header "Portfolio: ABCDE" should repeat on each page.

2. Show in groups of Master Ticket Number.

3. If Ticket Type is "Type1", a calculated summary needs to be shown.

4. Summary to be calculated as {Add amount if "Buy", Subtract if "Sell", Ignore if "Transfer"}

5. Output should be PDF

I am using PROC REPORT, and trying to do the following:

define Portfolio / ORDER NOPRINT;

define expiry / ORDER;

define master_ticket / ORDER;

define all others as DISPLAY;

compute before _page_;

line 'Portfolio: ' portfolio $6.;

endcomp;

compute after master_ticket;

line 'Net Position: ' @321 calculated_amount.sum comma15.2;

endcomp;

break after portfolio / page;

where in a previous data step, I have calculated the calculated_amount by multiplying the amount by +1, -1 or 0 based on Ticket Type.

However, I am facing the issues with following requirements;

1. When the Portfolio changes, a new page is started, but if the portfolio has many transactions, the portfolio does not repeat on the next page.

2. I am not able to show the summary only for Ticket Type1. The summary comes for all currently, and users want it removed for Type2.

3. I am not able to align the summary amount perfectly to the amount column since this is a customized summary.

Please let me know if you can help me with any of the above issues. Now I am wondering if PROC REPORT is the correct choice, or are there better suited choices for these kind of requirements?

Thanks!

Raj

Re: Proc Report - Help needed

Cynthia_sas — Wed, 13 Mar 2013 14:10:54 GMT

Hi:

In answer to your questions:

1) This could be destination dependent. The problem with the compute before _page_ is that if your destination is NOT a paged destination, then compute before _page_ works as you describe. But when I run the code below in SAS 9.3 and ODS PDF, I do correctly get the page string written at the top of the table on every page. I no longer have SAS 9.1.3 to test with. The only folks who are able to work with you on 9.1.3 issues are Tech Support or someone else who has 9.1.3.

2) When you do break processing in order to write either a summary line or a line, the break happens at EVERY break point. So, you cannot, for example, have a break line for TYPE1, but not for TYPE2. You may need to do one report for TYPE1 and a different report for TYPE2 tickets. I do not see ALL your PROC REPORT code. Do you even have TICKET_TYPE in the COLUMN statement?

3) Using @ pointer control in a LINE statement only works for LISTING output to "line up" what you write with the text above or below it. It will not work the same way in ODS destinations like HTML, RTF, PDF. So I suspect the reason you are not getting aligned values for your customized summary is that you are using ODS PDF.

You can manipulate the data to make transfer amount 0 and then make sell amount -999 and use that new variable to automatically summarize, somewhat as shown here using SASHELP.SHOES. When I use COMPUTE BEFORE _PAGE_ in ODS PDF and SAS 9.3, I do get the correct header at the top of every page in the PDF. Of course, since HTML is NOT a paged destination, I do only get the header at the beginning of the table. (Screenshot shows bottom of one page and top of next page with header)

I'd suggest that you work with Tech Support on this. They can take ALL your code and a sample of your data and run it in 9.1.3 to see if they can replicate your results.

cynthia

** make some data, but do NOT create any totals;

data final;

set sashelp.shoes;

where region in ('Asia','Western Europe', 'Eastern Europe');

** create a new variable with newsales based on product;

** create NEWSALES to be 0, -sales or +sales depending on TRANSTYPE;

** Also, make Europe have a lot of obs so it spans multiple pages;

if findw(region,'Europe') gt 0 then region = 'Europe';

if product = 'Boot' then do;

newsales=0;

transtype='Transfer';

end;

else if product in ("Women's Dress", "Women's Casual") then do;

newsales=-1*sales;

transtype = 'Sell';

end;

else do;

newsales=sales;

transtype = 'Buy';

end;

run;

options nodate nonumber orientation=portrait;

ods pdf file='c:\temp\changetot.pdf';

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

proc report data=final nowd;

title '1) Showing all vars and all totals';

column region product transtype newsales sales;

define region / order;

define product / display;

define transtype / display;

define newsales / sum;

define sales / sum;

break after region / summarize page;

run;

proc report data=final nowd;

title '2) Final Report with compute before _page_';

column region product transtype newsales sales;

define region / order noprint;

define product / display;

define transtype / display;

define newsales / sum noprint;

define sales / sum;

break after region / summarize page;

compute after region;

sales.sum = newsales.sum;

endcomp;

compute before _page_ / style={just=l font_weight=bold};

line 'region: ' region $25.;

endcomp;

run;

ods _all_ close;

Re: Proc Report - Help needed

sas_raj — Thu, 28 Mar 2013 12:03:43 GMT

Thank you Cynthia!

I am using ODS PDF, so I was expecting paging to work correctly. However, the ouput given in "compute before _page_" appears only when the value of break variable changes. It does not repeat when the group has huge number of records spanning multiple pages. I think I will have to go through the Tech Support route.

I haven't given the full code as this is a client's code and I need to be careful. However, yes the column statement has all the variables. The clients just want to see the summary only for some type of transactions.

Yes, I am using PDF and I think this is a minor issue. As long as they know which variable is being summarized, it is ok.

Re: Proc Report - Help needed

sas_raj — Mon, 01 Apr 2013 05:04:56 GMT

I think the issue I am facing is this one:

http://support.sas.com/kb/6/552.html

Anyway, I found a workaround. I added a "BY variable" in Proc Report, and so now the variables used in BY groups are available to the report title e.g. using #BYVAL(Portfolio). So instead of using compute before _page_ now I put the variable in report title.