How to delete sheet from excel using sas

[sam1231]

Hi ,

i Want to delete excelsheet from excel using sas. Can I do that?

How can i use DDE to delete sheet from excel using sas.

Thanks,

[SASKiwi]

Why not use PROC DATASETS? DDE only works if you are running SAS on your PC. PROC DATASETS works on both PCs and remote SAS servers.

libname MyExcel EXCEL "MyExcelWorkbook.xlsx";

proc datasets library = MyExcel nolist nowarn;
  delete MySheetName;
  run;
quit;

[sam1231]

thank you for your reply!

But it s give me below error.

ERROR: Connect: Class not registered ERROR: Error in the LIBNAME statement. 

i am using below code:

libname MyExcel EXCEL "c/users/download/report.xlsx";

proc datasets library = MyExcel nolist nowarn;
  delete MySheetName;
  run;
quit;





I have PC SERVER FILE installed.

[SASKiwi · Accepted solution]

Please go through the instructions in this SAS note to diagnose this error then post your findings: http://support.sas.com/kb/60/356.html

Are you using local SAS on your PC? If so then you should refer to your spreadsheet like this:

c:\users\download\report.xlsx

[LuciaCekanakova]

Hi,

I've tried your code to delete a sheet from excel - it runs without errors, but the sheet is not deleted from the excel file. Any idea why?

Thanks!

[Vince_SAS]

Can you post the exact code that you used, and the messages that you see in the log?

Vince DelGobbo

SAS R&D

[LuciaCekanakova]

My code:

libname MyExcel EXCEL "C:\excel_file.xlsx";

proc datasets library = MyExcel nolist nowarn;
  delete Sheet5;
  run;
quit;

libname myexcel;

And the whole log:

NOTE: Copyright (c) 2016 by SAS Institute Inc., Cary, NC, USA.
NOTE: SAS (r) Proprietary Software 9.4 (TS1M6 MBCS3170)
Licensed to SAS INTERNAL 940 19W04 WX6+ FULL PROD+GRID, Site 70146517.
NOTE: This session is executing on the X64_10PRO platform.

NOTE: Analytical products:

SAS/STAT 15.1
SAS/ETS 15.1
SAS/OR 15.1
SAS/IML 15.1
SAS/QC 14.3

NOTE: Additional host information:

X64_10PRO WIN 10.0.16299 Workstation

NOTE: SAS initialization used:
real time 3.34 seconds
cpu time 2.84 seconds

NOTE: Unable to open SASUSER.REGSTRY. WORK.REGSTRY will be opened instead.
NOTE: All registry changes will be lost at the end of the session.
WARNING: Unable to copy SASUSER registry to WORK registry. Because of this,
WARNING: you will not see registry customizations during this session.
NOTE: Unable to open SASUSER.PROFILE. WORK.PROFILE will be opened instead.
NOTE: All profile changes will be lost at the end of the session.
NOTE: This SAS session is using a registry in WORK. All changes will be lost at the end of
NOTE: this session.
NOTE: Unable to open SASUSER.PROFILE. WORK.PROFILE will be opened instead.
NOTE: All profile changes will be lost at the end of the session.

1 libname MyExcel EXCEL "C:\excel_file.xlsx";
NOTE: Libref MYEXCEL was successfully assigned as follows:
Engine: EXCEL
Physical Name: C:\excel_file.xlsx
2
3 proc datasets library = MyExcel nolist nowarn;
NOTE: Writing HTML Body file: sashtml.htm
4 delete Sheet5;
5 run;

6 quit;

NOTE: PROCEDURE DATASETS used (Total process time):
real time 1.05 seconds
cpu time 0.79 seconds

7
8 libname myexcel;
NOTE: Libref MYEXCEL has been deassigned.

[Vince_SAS]

Run this code and examine the output:

options validmemname=extend;

libname MyExcel EXCEL 'C:\excel_file.xlsx';

proc contents data=MyExcel._all_; run; quit;

The table name is MYEXCEL.'Sheet5$'n and 'Sheet5$'n is what you need to use in your code:

options validmemname=extend;

libname MyExcel EXCEL 'C:\excel_file.xlsx';

proc datasets library = MyExcel nolist nowarn;
  delete 'Sheet5$'n;
run; quit;

libname MyExcel;

The EXCEL engine deletes the contents of the worksheet, but does not remove the now empty worksheet from the file.

Vince DelGobbo

SAS R&D

[LuciaCekanakova]

Hi,

it works now, thank you!

Too bad it doesn't delete the sheet itself though.

Lucia

[Ksharp]

Try another engine.

libname MyExcel xlsx  "c/users/download/report.xlsx";

[Vince_SAS]

This error happens when the bitness of SAS does not match the bitness of Microsoft Excel.

Try using the XLSX engine, like @Ksharp suggested.

Vince DelGobbo

SAS R&D