topic Re: Error opening exel file in SAS Programming

Error opening exel file

tiffinbox — Fri, 18 May 2012 23:08:29 GMT

Has anyone seen or know how to fix this error? I'm new to SAS and have been having this issue for weeks now. I have tried creating a new file and deleting the old one and sometimes it works, but only after many attempts. I can usually use SAS for about 15 min until it crashes. I have a lot of variables and data, but I thought SAS was powerful enough to handle the data. I've also tried reinstalling and that didnt' work.

Re: Error opening exel file

mbusa — Wed, 04 Dec 2013 14:47:46 GMT

HI,

does anyone have any feedback on the above. I created an excel workbook from SAS datasets and nobody can open the excel file excep for myself.

Any feedback is much appreciated.

Many Thanks,

Michelle

Re: Error opening exel file

ballardw — Wed, 04 Dec 2013 15:45:33 GMT

It would be helpful to know exactly HOW you are creating the Excel file, post code examples, find the generated code from EG. Also the version of SAS.

Re: Error opening exel file

mbusa — Mon, 09 Dec 2013 18:31:54 GMT

Hi,

Thanks so much for your reply. Below is the code I am using. I am not using EG just SAS :-).

OPTIONSNOFMTERR NOCENTERDATE MPRINT MLOGICSYMBOLGEN;

/*CREATE EXCEL WORKBOOK FOR ALL DATASETS*/

%MACRO LIBDATA;

/* Create Libraries */

LIBNAME CONV "D:\Program\DataTransfers\sd2";

/* Get contents for data sets (from one of the libraries), save result in an output data set */

PROC CONTENTS DATA=CONV._ALL_ MEMTYPE=data OUT=OUT NOPRINT;

RUN;

/* Sort prior to selecting unique data set names */

PROC SORT DATA=OUT;

BY MEMNAME
VARNUM;

RUN;

/* Select unique data set names, remove unneeded datasets */

DATA A; SET OUT;

BY MEMNAME
VARNUM;

/* Each variable in a data set produces an observation in

the output data set, remove the duplicate MEMNAMEs. */

IF FIRST.MEMNAME;

RUN;

/* Create data set names as macro variables & get total number of data sets */

DATA _NULL_;

SET A END=LAST;

BY MEMNAME VARNUM;

/* Create a macro variable DS1 with the value of MEMNAME */

CALL SYMPUT('DS'||LEFT(_N_),TRIM(MEMNAME));

/* Create a macro variable for the total # of datasets */

IF LAST THEN CALL SYMPUT('TOTAL',LEFT(_N_));

RUN;

ods _all_ close;
ods tagsets.excelxp path='D:\program\DataTransfers\xls'
file='SASDATA_02DEC2013.xml'

style= Printer;

%DO i=1 %TO &total;

ods tagsets.ExcelXP options(Doc = 'Help' sheet_name="&&DS&I"
width_fudge='0.50''landscape' row_repeat='1-2');

proc print data=conv.&&DS&I label noobs;

run; quit;

%END;

Ods tagsets.excelxp close;

%MEND LIBDATA;

/* Call the macro */

%LIBDATA;

Re: Error opening exel file

SASKiwi — Mon, 09 Dec 2013 19:09:34 GMT

Try just using an ODS statement like this. You don't need both path and file parameters.

ods tagsets.excelxp file='D:\program\DataTransfers\xls' style= Printer;

Re: Error opening exel file

SteveNZ — Mon, 09 Dec 2013 20:08:27 GMT

Try (untested): No need to set the macro each time. Also do your users realise they're opening an XML file and not XLS? You can give it a xls extension instead or convert it to excel using VB script or similar.

DATA _NULL_;

SET A ;

CALL SYMPUT('DS'||LEFT(_N_),TRIM(MEMNAME));

CALL SYMPUT('TOTAL',LEFT(_N_));

RUN;

%macro libdata ;

%DO i=1 %TO &total;

ods tagsets.ExcelXP options(sheet_name="&&DS&I"

width_fudge='0.50''landscape' row_repeat='1-2');

proc print data=conv.&&DS&I label noobs;

run;

quit;

%END;

%mend libdata ;

ods _all_ close;

ods tagsets.excelxp file='D:\program\DataTransfers\xls\SASDATA_02DEC2013.xml' style= Printer;

%libdata ;

Ods tagsets.excelxp close;

Re: Error opening exel file

Reeza — Mon, 09 Dec 2013 20:13:03 GMT

What version of Excel do you have?

Your file is being saved as XML so that can cause some issues, you're better off saving it as XLSX afterwards if possible before distributing the file.

If the data contains more than 60K rows per sheet and you have Excel 2003 then you will definitely have issues.