topic Re: How to Export two datasets data to specific range/cells in Excel in New SAS User

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

anjicam — Wed, 12 Dec 2018 16:55:59 GMT

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.

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

Reeza — Wed, 12 Dec 2018 17:05:29 GMT

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

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

art297 — Wed, 12 Dec 2018 18:25:37 GMT

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

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

anjicam — Wed, 12 Dec 2018 18:41:49 GMT

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

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

anjicam — Wed, 12 Dec 2018 18:44:51 GMT

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

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

anjicam — Thu, 13 Dec 2018 06:19:33 GMT

I tried RANGE & SHEET but it throws error

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

Reeza — Thu, 13 Dec 2018 16:11:33 GMT

Show your code please.

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

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

anjicam — Fri, 14 Dec 2018 07:21:19 GMT

/*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;

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

Reeza — Fri, 14 Dec 2018 16:12:10 GMT

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

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

anjicam — Sat, 15 Dec 2018 05:43:14 GMT

/*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?

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

Tom — Sat, 15 Dec 2018 16:53:45 GMT

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.