topic Re: Issue in Proc report in ODS and Base Reporting

Issue in Proc report

SJN — Sun, 25 Feb 2018 15:18:44 GMT

Hi All,

I'm new to the sas below is my sample data and output format which I'm looking for. I tried to work on proc report which worked when my data is very low say 200 observations. But, when I applied on the whole date which contains around 32,000 observations my enterprise guide is not responding.

Please help me to resolve this issue.

proc report data=a1 nowd out=a3;
by Amount transaction_date
 ;
column customer Amount pmt_amt  transaction_date
 System_date;
define customer/order;
define transaction date
/order;
define System_date/order;
/*break after vin/skip summarize ol;*/
rbreak after/summarize dol;
run;

customer	Amount	transaction date	System date
1234	100	02-Aug-17	02-Aug-17
1234	200	03-Aug-17	03-Aug-17
2345	300	03-Aug-17	03-Aug-17
3214	200	01-Sep-17	01-Sep-17

looking for Output:

1234
transaction date	System date	Amount
02-Aug-17	02-Aug-17	100
03-Aug-17	03-Aug-17	200
Total		300
2345
03-Aug-17	03-Aug-17	300
Total		300
3214
01-Sep-17	01-Sep-17	200
Total		200

Looking forward to hear from you

Re: Issue in Proc report

PaigeMiller — Sun, 25 Feb 2018 15:33:26 GMT

You are asking PROC REPORT to do a huge amount of work, and if you are sending the results to the default HTML destination, then it also has to create a huge HTML file, so I'm not surprised it will take a huge amount of time. What happens if you change the output destination to turn off HTML and just send it to the LISTING destination?

Re: Issue in Proc report

SJN — Sun, 25 Feb 2018 18:45:10 GMT

Will try that. While using proc report I used OUT option to move the data to SAS data set. It's not even working.

Let me google for the listing and see how it works. My final output must be in excel.

Re: Issue in Proc report

PaigeMiller — Sun, 25 Feb 2018 20:01:11 GMT

@SJN wrote:

It's not even working.

This tells us nothing that we can use to help you. You need to give us details.

But if your final output needs to be in Excel, why are you writing this to an output (HTML or LISTING) anyway? Just write it to Excel (if that will work with this many lines) using ODS Excel and skip the output to HTML or LISTING.

Oh yeah, if you want to write it to Excel, you could have (hint hint hint) stated this in your very first post, instead of misleading us that you want it written to some other destination.

Re: Issue in Proc report

Cynthia_sas — Sun, 25 Feb 2018 20:43:46 GMT

Hi:
By default, Enterprise Guide always has the SASReport type of output always turned on. If you are running EG 7.1 (or higher) and using SAS 9.4 M2 (or higher), you can create an ODS Excel output file directly by going to Tools--> Options and turning off the SASReport format and turning on the ODS Excel format.

If you want a report, I am not sure why you are also adding in a SAS data set using OUT=. My suggestion is to try the code with only 1 destination turned on in EG and without OUT= and see whether that works better for you.

If you are running a version of SAS earlier than 9.4 M2, then you might need to switch to ODS CSV or ODS TAGSETS.EXCELXP as your destination, if you want the report output in Excel.

Cynthia

Re: Issue in Proc report

Ksharp — Mon, 26 Feb 2018 12:37:01 GMT


data have;
infile cards expandtabs truncover;
input customer 	Amount	transaction_date : $20. System_date : $20.;
cards;
1234	100	02-Aug-17	02-Aug-17
1234	200	03-Aug-17	03-Aug-17
2345	300	03-Aug-17	03-Aug-17
3214	200	01-Sep-17	01-Sep-17
;


proc report data=have nowd;
column customer transaction_date  System_date 	Amount;
define customer/group noprint;
compute before customer/style={just=left};
_customer=put(customer,best32. -l);
line _customer $32. ;
endcomp;
compute after customer/style={just=right};
line 'total:                                          ' amount.sum ;
endcomp;
run;