Here is a template that should work for you: ods _all_ close; ods tagsets.ExcelXP path='PATH' file='OUTPUT.xml' style=printer; ods tagsets.ExcelXP options(sheet_name='SHEET NAME ONE' AUTOFILTER = 'ALL'); proc print data=FILE1 noobs; var _all_;run;quit; ods tagsets.ExcelXP options(sheet_name='SHEET NAME TWO' AUTOFILTER = 'ALL'); proc print data=FILE2 noobs; var _all_;run;quit; ods tagsets.ExcelXP close; ... View more

Change the WHERE to AND to make the additional condition part of what determines if the value from the right hand table meets the join criteria. ... View more

Just an aside: gzip -c streams the output to stdout, so "gzip -dc filename" in a filename pipe is the perfect way to stream the decompressed output into the data step. Saves any additional disk space gzip is one of those "don't leave home without it" open source utilities. ... View more

You throw a big junk of code at us in a "development" state and without any comments in the code. You don't provide any data so that the code is actually executable for us and we can replicate what you describe - and then test what we eventually would suggest. You need to provide some test data (best as a data step creating this data) and then strip down your Proc Report with the ODS to the bare minimum so that it shows the challenge you're facing with the minimum code required. And then you tell us what you would want to get. If you help us to help you then I'm sure someone will be able to provide a solution. ... View more

Hi all, Thanks for all valuable input. I am working on it. I am not able to post the data or code bcaz its very lengthy. my script to run many reports excel[near about 500+] and after that 500+ charts for related area's in PDF. script run fine but at last it show error reaching 440's area during creating charts. i have changed memsize in config file as 0 , max, and 2G, but nothing work. I am still working on that . once again thanks for your valuable input. ... View more

No probs.  Glad it helped.  Once you understand that the datastep can be used like a loop then it removes the need for long macro strings and splitting those out.  The call execute (and there is also call sub in newer SAS editions) are quite powerful.  I use them a fair bit and generate whole programs from metadata - a few lines of code, and a table of metadata can write lots of code out.  For instance my code above could become: data _null_;      set area;      call execute('proc report data=my_data;                               where area="'||strip(area)||'";                                ...'); run; If for New Jersey NJ you only want vara and varb output; but varc for the others: data _null_;      set area;      call execute('proc report data=my_data;                              where area="'||strip(area)||'";');      if area ne "New Jersey, NJ" then call execute(' column vara varb varc;...');      else call execute(' column vara varb; ...'); run; ... View more

Just add a blank label to Area: define area / ' ' across order=data; ... View more