topic Populating a template excel file with another excel file in SAS Procedures

Populating a template excel file with another excel file

vomer — Fri, 17 Feb 2012 17:15:25 GMT

Hi Guys,

I am wondering if this is possible?

Basically if I have 2 excel files : File1.xls and File2.xls

Can I use SAS to say: Take all rows/columns from file1.xls and put them (starting at a specific cell) in File2.xls?

The next part of my question is...can I do the same for specific cell ranges? So Cells A1:A10 from File1.xls to Cells N1:N10 in File2.xls

Please provide sample codes if possible

Thanks!

Populating a template excel file with another excel file

art297 — Fri, 17 Feb 2012 17:28:16 GMT

Does your site have SAS Access/PC files?

Populating a template excel file with another excel file

vomer — Fri, 17 Feb 2012 18:22:46 GMT

Yes they do.

Populating a template excel file with another excel file

art297 — Fri, 17 Feb 2012 19:31:17 GMT

Not sure why I asked about SAS Access, as the export procedure doesn't allow one to use a range statement.

Thus, I used DDE to export the file. If you're not familiar with DDE, you can find numerous examples on the web.

I took a shortcut and simply opened the desired output file manually. You would probably want to do all of that programmatically .. which is definitely possible.

Below, I use proc import to import the desired range, then output it to a different range on another spreadsheet:

PROC IMPORT OUT= WORK.have;

DATAFILE= "C:\art\abc.xls"

DBMS=excel REPLACE;

range='feb2012$A1:A27'n;

GETNAMES=no;

Run;

filename dataout dde "excel|[cba.xls]Sheet1!r1c14:r27c14" notab;

data _null_;

set have;

file dataout;

put f1 $20.;

run;