How to Export two datasets data to specific range/cells in Excel

[anjicam]

I have two datasets say DS_A  and DS_B .

Both datasets contains 3 variables & 10 observations each.

I wish to export these datasets data to only one Excel file ( File_name : Sample.XLS, SheetName: Sheet1 )

I need to Export DS_A data to Sheet1 from A1:C10  cells and DS_B data to Same Sheet1 from A15:C25 cells .

the Challenge here is i shouldnt use DDE concept. 

[Reeza]

Have you tried the RANGE or SHEET statements on PROC EXPORT?

[anjicam]

I tried RANGE & SHEET but it throws error

[Reeza]

Show your code please.

---

@anjicam wrote:
I tried RANGE & SHEET but it throws error

---

[anjicam]

/*Sample Datasets*/
DATA INVENTORY;
INPUT PARTNUMBER $ DESCRIPTION $ INSTOCK @17 PRICE;
DATALINES;
K89R SEAL 34 245.00
M4J7 SANDER 98 45.88
LK43 FILTER 121 10.99
MN21 BRACE 43 27.87
BC85 CLAMP 80 9.55
NCF3 VALVE 198 24.50
KJ66 CUTTER 6 19.77
UYN7 ROD 211 11.55
JD03 SWITCH 383 13.99
BV1E TIMER 26 34.50
;
RUN;

DATA TOTAL;
INPUT CATEGORY:$ 15. TOTAL;
DATALINES;
STOCK_IN_TOTAL 1200
PRICE_IN_TOTAL 443.60
;
RUN;

Proc Export Data= Work.Inventory
Outfile= "C:\Users\U518651\Desktop\test\Inventory_Test.xls"
dbms=xls
replace;
sheet=TEST;
quit;

Proc Export Data= Work.Total
Outfile= "C:\Users\U518651\Desktop\test\Inventory_Test.xls"
dbms=xls
replace;
/*sheet=TEST;*/
Range='Test$A15:B16';
quit;

[Reeza]

Try XLSX not XLS, I'm not sure XLS supports that type of export. XLS is a fairly old file format (10 years now).

[anjicam]

/*Sample Dataset*/
DATA INVENTORY;
INPUT PARTNUMBER $ DESCRIPTION $ INSTOCK @17 PRICE;
DATALINES;
K89R SEAL 34 245.00
M4J7 SANDER 98 45.88
LK43 FILTER 121 10.99
MN21 BRACE 43 27.87
BC85 CLAMP 80 9.55
NCF3 VALVE 198 24.50
KJ66 CUTTER 6 19.77
UYN7 ROD 211 11.55
JD03 SWITCH 383 13.99
BV1E TIMER 26 34.50
;
RUN;
/*Tried xlsx and sheet and range
still it gives error at Range*/
Proc Export Data= Work.Inventory
Outfile= "D:\SAS\Others\Inventory.xlsx"
dbms=xlsx
replace;
sheet=TEST;
Range='Test$A15:B16';
quit;
/*Tried xlsx and range
still it gives error at Range*/
Proc Export Data= Work.Inventory
Outfile= "D:\SAS\Others\Inventory.xlsx"
dbms=xlsx
replace;
Range='Test$A15:B16';
quit;

Can we use RANGE in Proc Export?

[Tom]

Your range is only two columns and two rows.  How are you going to fit that dataset into that range?

Also try just setting the upper left hand corner on RANGE.

[art297]

Are you on a Windows' operating system and, if so, is the use of VB script acceptable? If the answer to both questions is yes, there's a macro for that: http://www.sascommunity.org/wiki/A_Poor/Rich_SAS_Users_Proc_Export

[anjicam]

I tried sheet n range option
But i see error in log at the word sheet n range

[anjicam]

Existing code is using DDE concept(which run on sas 9.4) to write data to particular range of cells in existing workbook

Now i need to automate this to run in sas EG 7.1
Instead of DDE , i need to use other exporting strategy