Error opening exel file

Reply
N/A
Posts: 1

Error opening exel file

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.

New Contributor
Posts: 3

Re: Error opening exel file

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

Super User
Posts: 10,472

Re: Error opening exel file

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.

New Contributor
Posts: 3

Re: Error opening exel file

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;

Super User
Posts: 3,101

Re: Error opening exel file

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;


Frequent Contributor
Posts: 87

Re: Error opening exel file

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;

Super User
Posts: 17,759

Re: Error opening exel file

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.

Ask a Question
Discussion stats
  • 6 replies
  • 262 views
  • 0 likes
  • 6 in conversation