BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
robertrao
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

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

View solution in original post

8 REPLIES 8
robertrao
Quartz | Level 8

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
Onyx | Level 15

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

Haikuo

robertrao
Quartz | Level 8

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
Onyx | Level 15

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
Opal | Level 21

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
Quartz | Level 8

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
Onyx | Level 15

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
Quartz | Level 8

Great.

Thanks a lot for the help.

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
  • 8 replies
  • 2958 views
  • 6 likes
  • 3 in conversation