Proc Export-Error

[robertrao]

Hi,

I have a dataset with 160 patients, Now I want to export into Excel and use the following code; I can see that the sheet was created,

BUT

when i try to open up the Excel sheet it says" File cannot be acessed, file may be corrupted or located on a server that is not responding or read only!!!!

Could you help me resolve tghis issue???

43  proc export data=procedures_descrip2
344  outfile="C:\Data\Projects\Colect-20121011001(2)\sample_out.xls"
345  dbms=excel replace;
346  sheet=colect;
347  run;

NOTE: "COLECT" range/sheet was successfully created.
NOTE: PROCEDURE EXPORT used (Total process time):
      real time           3.57 seconds
      user cpu time       3.07 seconds
      system cpu time     0.12 seconds
      Memory                            795k
      OS Memory                         16240k
      Timestamp            10/25/2012  11:43:12 AM

[robertrao]

Hi,

I got it.

Now there is another issue. The formats are not being applied after the dataset is converted to EXCEl. How can I get the formats applied??

Thnx

[Haikuo]

You can't if you use proc export. Try ODS excelxp tagset instead.

Haikuo

[robertrao]

Hi,

Could you tell me how to use it if I want to convert this followibg dataset to Excel file???

procedures_descrip2

IS THIS THE RIGHT WAY TO USE IT???

ods tagsets.excelxp file='C:\temp\sample_out.xls';

proc print data=procedures_descrip2;

run;

ods tagsets.excelxp close;

[Haikuo]

Yes, that is about right. I would replace .xls with .xml, so when you double-click opening it, there wouldn't be any pop-up warning, as your xls is not genuine xls. And when you really want xls, you can 'save as'.

There are many SGF papers online, google it: filetype:pdf ods excelxp tagset

Any one of them will get you jump-started.

Haikuo

[art297]

Karun,

I think that the note may reflect a 32-bit/64-bit collision.  If so, some possible work arounds are listed at:

http://support.sas.com/kb/40/383.html

Specifically, they suggest using:

proc export data=test outfile="c:\public\myfiles.xls" dbms=xls replace;

sheet="mysheet";

newfile=yes;

version=2003;

run;

[robertrao]

Hi,

Thanks for the reply. I got the answer using ods tagsets...

I have a question though?

Does ODS tagsets work only with the Proc steps??IBelow I used the final dataset in the proc print with ods tagsets..CAN I USE IT WITH THE PROC STEP WHICH CREATES THE FINAL DATASET...(procedures_descrip2)

Secondly,what does this Note mean:

371  ods tagsets.excelxp file="C:\Data\Projects\Collect-20121011001(2)\collect.xls";
NOTE: Writing TAGSETS.EXCELXP Body file: C:\Data\Projects\Collect-20121011001(2)\collect.xls
NOTE: This is the Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.94, 09/09/12). Add
options(doc='help') to the ods statement for more information.
372  proc print data=procedures_descrip2;
373  run;

NOTE: There were 52829 observations read from the data set WORK.PROCEDURES_DESCRIP2.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           4:24.25
      user cpu time       4:02.75
      system cpu time     1.17 seconds
      Memory                            808k
      OS Memory                         18544k
      Timestamp            10/25/2012  12:35:45 PM

374  ods tagsets.excelxp close;

[Haikuo · Accepted solution]

I am not sure if I understand your question. Here is my bold guess of your question:" Can excelxp.tagset can also be used in something other than Procs? Such as Data step?". If that was the question, then the answer is 'Yes':

ods listing close;

ods Tagsets.ExcelXP file='h:\temp\test.xml';

title 'Listing of Class data';

data _null_;

set sashelp.class;

file print ods;

put _ods_;

run;

ods Tagsets.ExcelXP close;

ods listing;

Haikuo

[robertrao]

Great.

Thanks a lot for the help.