sas enterprise 5.1 guide export to excel template

Question

I'm having a wee bit of a problem and I was hoping someone out there has a magic solution.

At present we run a bit of code on SAS 9.3 that uses DDE to populate an excel template - populating text from cells B2:M24 - these are character or text fields with no formulae. This does work, but we're moving to Enterprise Guide 5.1 and I need something that will work on there.

I'm pretty new to Enterprise Guide and I've googled this until my head is about to explode and so far, nothing has worked. We can't just do a manual export as this will be a scheduled job.

Presumably there is a way to do this that I'm missing. Can anyone point me in the right direction please?

SAS clarifications: Enterprise Guide 5.1 would still use SAS 9.3 backend, correct? Are you using a SAS server on that backend, or is it using a local PC SAS installation? Do you have ACCESS to PC FILES licensed, and is this a Windows Server (if it's a server) or Desktop? — Joe
Also, it would be helpful to clarify some details about what you're doing. Are there cells outside of B2:M24 that are populated? What are they populated with - is it just column headers, or is it something more (formulas, charts, ...)? What prevents you from doing a straight export? — Joe
Sorry, it's been a long day. We're using a SAS server with access to PC files licence on a Windows Server. The cells outside of the range are populated, which seems to be the problem. — ficm
And in case it makes a difference it's a 32 bit machine (the server is 64 bit), and Excel 2010 which is making it even more fun. — ficm
Are you using PC Files Server then to do your proc imports/exports? — Joe

ficm ficm · Accepted Answer · 2015-08-25T17:37:59

Managed to get it done through PC Files server using named range in Excel.

 libname xls PCFILES path="C:\Template.xls" server=<Server> port=<port> 
  user= pass=   ;


      proc datasets lib=xls nolist;
         delete <named range>;
      quit;

      data xls.<named range>;
      set <named range>;
      run;
libname xls clear;

Probably not the most elegant way but it got the job done.

Thanks anyway.

sas enterprise 5.1 guide export to excel template

1 Answers