Help using Base SAS procedures

Populating a template excel file with another excel file

Reply
Frequent Contributor
Posts: 117

Populating a template excel file with another excel file

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 Smiley Happy

Thanks!

PROC Star
Posts: 7,366

Populating a template excel file with another excel file

Does your site have SAS Access/PC files?

Frequent Contributor
Posts: 117

Populating a template excel file with another excel file

Yes they do.

PROC Star
Posts: 7,366

Populating a template excel file with another excel file

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;

Ask a Question
Discussion stats
  • 3 replies
  • 132 views
  • 0 likes
  • 2 in conversation