BookmarkSubscribeRSS Feed
tiffinbox
Calcite | Level 5

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.

6 REPLIES 6
mbusa
Calcite | Level 5

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

ballardw
Super User

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.

mbusa
Calcite | Level 5

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;

SASKiwi
PROC Star

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;


SteveNZ
Obsidian | Level 7

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;

Reeza
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 644 views
  • 0 likes
  • 6 in conversation