Export results to a named range in excel

[DME790]

Hi All,

I have seen this written about but can't get it to work. The Microsoft SAS Add-In has been disabled at work so I can't use that.

My spreadsheet is saved to the server /sasdata/Report/ExpansionReportDRAFTv01.xlsx.

The named range is called SASData and is in the tab called DATA in the spreadsheet.

I get the following error when I run the LIBNAME statement:

ERROR: The EXCEL engine cannot be found.
ERROR: Error in the LIBNAME statement.

And not sure if I have the correct code in the data step. 

Libname XL excel "/sasdata/Report/ExpansionReportDRAFTv01.xlsx";


Data XL.SASData;
	Set work.Succ_Answered_AHT_TransferMerge;
RUN;

Any help is appreciated.

Cheers

Dean

[Tom]

Did you try using the XLSX engine instead?

[DME790]

HI Tom,

Thanks for a prompt response and happy New Year.

I didn't think XLSX worked as it didn't turn blue - but just changed it and it has sent data to the right tab but has returned mainly zero's.

Do you know why it would do that?

WEEK_END_DT	NetTotal_Calls	NetTalk_Time	NetWORK_TIME	NetHOLD_TIME	NetTHT
1/01/1960	0	0	0	0	2.54400455180
1/01/1960	0	0	0	0	1.192E+286
1/01/1960	0	0	0	0	-6.3113E-100
1/01/1960	0	0	0	0	5.15093E+39
1/01/1960	0	0	0	0	-3.3819E+134
1/01/1960	0	0	0	0	1.5125E-122
1/01/1960	0	0	0	0	7.45859E+74

[Tom]

Don't trust the editor to understand SAS code. The editor does not actually call the SAS language.  It is just applying some formatting rules that someone taught it.

No idea why it would change your data. 

Can you create an example XSLX file and a SAS dataset (in the form of a data step with inline data)?

[DME790]

Hi @Tom and @Reeza

I made a test dataset with the same data and XLSX code and it exports the data correctly.

Reeza - Most of the output should be full numbers but when I do expand the decimal places they are all 0's.

data test;
	Input WEEK_END_DT $ TOTAL_CALLS TALK_TIME WORK_TIME HOLD_TIME NetTotal_Calls NetTalk_Time NetWORK_TIME NetHOLD_TIME NetTHT NETAHT EXPTotal_Calls EXPTalk_Time EXPWORK_TIME EXPHOLD_TIME ExpTHT EXPAHT NetworkTransfers PilotTransfers EXPTransPct NETTransPct transfer_Total ToBeEntered ToBeAnswered ToBeAbandoned ToBeAnswerTM ToBeAbandonTM ToBeASA ToBeAba SUCCESS_CONTACT_CNT BUSY_CONTACT_CNT;
	Datalines;
19Nov2017 17593 7898186 2054557 1874257 16188 7244698 1868082 1771885 10884665 672.39096862 1405 653488 186475 102372 942335 670.70106762 2760 116 0.9174377224 0.8295033358 3645 26924 17760 8902 38949957 13222610 2193.1282095 1485.3527297 27246 469121
26Nov2017 21938 9797660 2682971 2376797 20193 8964016 2405889 2253054 13622959 674.63769623 1745 833644 277082 123743 1234469 707.43209169 3427 136 0.9220630372 0.8302877235 3563 33194 22064 10808 47923262 16124239 2172.011512 1491.8799963 27471 482776
03Dec2017 22442 10120491 2762996 2413112 20135 8977422 2409353 2228902 13615677 676.21936926 2307 1143069 353643 184210 1680922 728.61811877 3348 189 0.9180754226 0.833722374 3537 34375 22543 11495 49593373 17951002 2199.9455707 1561.6356677 24621 527414
10Dec2017 21357 9737594 2764728 2217501 18854 8620049 2403162 2041517 13064728 692.94197518 2503 1117545 361566 175984 1655095 661.24450659 3020 180 0.9280862964 0.8398217885 3200 33065 21473 11206 51131662 16861642 2381.2071904 1504.697662 26105 535590
17Dec2017 21839 10201589 2822872 2412485 19299 8978694 2465897 2233783 13678374 708.76076481 2540 1222895 356975 178702 1758572 692.3511811 2928 176 0.9307086614 0.8482822944 3104 33276 21954 10924 50120596 15226958 2282.9824178 1393.8994874 33882 408364
24Dec2017 24427 10837289 2960926 2430812 21598 9443962 2525376 2207239 14176577 656.38378554 2829 1393327 435550 223573 2052450 725.50371156 3095 210 0.9257688229 0.8566996944 3305 35293 24518 10392 49328139 12762791 2011.9152867 1228.1361624 53106 282143
31Dec2017 10656 4593985 1287321 1108205 9779 4201907 1191336 1073041 6466284 661.24184477 877 392078 95985 35164 523227 596.61003421	1468 90 0.897377423 0.8498824011 1558 14384 10692 3552 17755044 3493323 1660.5914703 983.48057432 26227 58369
07012018 17895 7879222 2254425 1917033 16046 7058717 2061466 1818201 10938384 681.68914371 1849 820505 192959 98832 1112296 601.56625203 2334 131 0.9291508924 0.8545431883 2465 32157 23608 8120 38305294 7325632 1622.5556591 902.17142857 41920 189301
;
RUN;

Libname XL xlsx "/sasdata/misc/EMSToBeReport/Test.xlsx";

Data XL.SASData;
	Set Test;
RUN;

Libname xl Clear;

However w2hen I run it in the actual program it returns 0 in the majority of cells - I have attached the excel spreadsheet from the original export.

Don't understand why the test data would export OK and the other wouldn't.

Any ideas on why?

Thanks for help so far.

Cheers

Dean

[Tom]

Don't understand why the test data would export OK and the other wouldn't.

Are you sure that you didn't actually write the zeros?  Did you check the data you tried to write into the XLSX file?

[DME790]

Hi @Tom,

This is what shows in the 'Output Data' tab in EG. I have added extra dates in hence the blanks

So as far as I know it is OK. If I use ODS Excel file="/SAS/........... The data is exported out OK

[Tom]

That doesn't really help much as I have no idea what "output data" you browsed. In your original post you were trying to write from a work dataset to the XLSX file using a libname.  

libname XL xlsx "/sasdata/Report/ExpansionReportDRAFTv01.xlsx";
data XL.SASData;
  set Succ_Answered_AHT_TransferMerge;
run;

Why would the input data that you were writing to the XLSX file show up in the Output Data tab?  Are you saying that EG can browse the XLSX file and it looks ok, but when you open it in Excel the values have been converted to zeros?

Are you saying that ODS EXCEL can write to a named range?  That would be interesting, but surprising to me.

Or are you saying the ODS EXCEL can create a new working XLSX file, but the XLSX libname engine cannot?

Can you just run a PROC PRINT on the data you are trying to copy into XLSX named range and verify that the data you are copying does not have zeros?  Also run PROC CONTENTS on the data you are copying and make sure that the variable names match up with the formatting that the named range has for those columns.  Perhaps the columns in your SAS dataset are in a different order than the Excel template expects and that is confusing SAS or Excel.

[DME790]

Hi @Tom

Thanks for your help and apologies for the confusion.

I have used ODS EXCEL  just to get the data into a separate excel sheet and using VBA to import it into the workbook I'm using so I can start working with it.

When using XLSX, from the same Program as ODS Excel, it exports into the working workbook but with 0's . 

This is what I see in the output tab in EG

and if I use the below code it exports it to a file called ExpansionReportDRAFTv01b.xlsx

ods excel file="/sasdata/Report/ExpansionReportDRAFTv01b.xlsx";

Proc Print data=work.Succ_Answered_AHT_TransferMerge Noobs; 
RUN;

ODS EXCEL Close;

Hope that makes more sense.

Cheers

Dean

[Reeza]

Excel likely formatted it for you. Expand the number of decimals. When you export to a range it can assume the format in that range, modifying the template should fix that.