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
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
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
You can't if you use proc export. Try ODS excelxp tagset instead.
Haikuo
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;
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
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;
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;
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
Great.
Thanks a lot for the help.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.