The SAS Output Delivery System and reporting techniques

Issue in Proc report

Accepted Solution Solved
Reply
Contributor SJN
Contributor
Posts: 30
Accepted Solution

Issue in Proc report

 

 

 

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

 

 


Accepted Solutions
Solution
‎05-16-2018 03:32 AM
Super User
Posts: 10,849

Re: Issue in Proc report


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;

View solution in original post


All Replies
Respected Advisor
Posts: 3,268

Re: Issue in Proc report

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?

--
Paige Miller
Contributor SJN
Contributor
Posts: 30

Re: Issue in Proc report

[ Edited ]
Posted in reply to PaigeMiller

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.

Respected Advisor
Posts: 3,268

Re: Issue in Proc report


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.

--
Paige Miller
SAS Super FREQ
Posts: 9,428

Re: Issue in Proc report

Posted in reply to PaigeMiller
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
Solution
‎05-16-2018 03:32 AM
Super User
Posts: 10,849

Re: Issue in Proc report


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;

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 174 views
  • 0 likes
  • 4 in conversation