topic Re: Output tables as Excel files in SAS Procedures

Output tables as Excel files

nicnad — Wed, 01 Aug 2012 21:04:37 GMT

Hi,

I have the following SAS code that create individual tables based on a variable value (thanks to Haikuo for writing this code, you can see the full thread here https://communities.sas.com/message/133732#133732) :

proc sql NOPRINT;

select quote(CATS(name)) into :var separated by ',' from dictionary.columns where libname='SASUSER' AND MEMNAME='TEST';

quit;

data _null_;

dcl hash h(ordered: 'a');

h.definekey('_n_');

h.definedata (&VAR);

h.definedone();

do _n_=1 by 1 until (last.entity);

set SASUSER.TEST;

by entity notsorted;

h.add();

end;

h.output (dataset:'sasuser.'||compress(entity,,'kda'));

run;

As of right now the output are SAS tables and they are created in the SASUSER dataset. Is there an easy way to output all those tables to Excel files instead?

For example purpose the hard drive location can be c:\test\

Thank you for your help!

Re: Output tables as Excel files

Cynthia_sas — Wed, 01 Aug 2012 21:31:36 GMT

Hi:

Have you looked at ODS? Or PROC EXPORT? Or the SAS Libname Engine for Excel? All of those methods of getting a data table into Excel have been discussed and demo'd on the forum. If you want "data to data" conversion, then PROC EXPORT or the LIBNAME engine will create true, binary Excel files for you from your SAS data table. If, however, you want colors and fonts and titles in your Excel workbook/worksheet, then ODS methods (such as ODS TAGSETS.EXCELXP or ODS MSOFFICE2K) will create result tables (such as from PROC PRINT) and your colors and formats and titles can still be used.

It really depends on what you want to do and whether you have the SAS/Access modules to use EXPORT/LIBNAME engine methods. If you have BASE SAS, then you have ODS for the ODS methods.

cynthia

Re: Output tables as Excel files

nicnad — Wed, 01 Aug 2012 21:40:51 GMT

Thank you for your quick reply.

I have looked at those functions, but my understanding (I am a real beginner in SAS) is that I will have to write a line of code for every SAS tables that have already been created with the code writen by Haikuo.

What I really want to do, is not to write the code to export each sas tables one by one to Excel, but to replace this line :

h.output (dataset:'sasuser.'||compress(entity,,'kda'));

So instead of outputing directly in my work library in SAS format, it would export directly to my hard drive in Excel format.

I am sure I am not fully understanding the functions stated above, but I am pretty sure that the datasets can be exported automatically to an excel file (keeping the name given by the variable entity). Could you please help me write this code?

Thank you for your help and time.

Re: Output tables as Excel files

art297 — Wed, 01 Aug 2012 21:45:30 GMT

Is SAS/Access for PC File Formats included in your SAS license?

Re: Output tables as Excel files

nicnad — Thu, 02 Aug 2012 14:41:00 GMT

Yes I think it is.

Re: Output tables as Excel files

Kfhansen — Thu, 02 Aug 2012 16:30:14 GMT

Just an alternative.

You could also use the SAS Add-in for MS-Office.

Re: Output tables as Excel files

nicnad — Thu, 02 Aug 2012 17:18:10 GMT

Thank you for the reply LinLin.

I am not able to make it work and I get those error messages :

55 libname new "c:\test\&dsn..xls";
NOTE: Library NEW does not exist.

59          new.SASUSER BLUE_CARS_TABLE
                        _____________________
                        22
ERROR 22-322: Syntax error, expecting one of the following: ',', TABLE, VIEW.

63            data new.&dsn;
64
65              set sasuser.&dsn;
ERROR: File SASUSER.SASUSER.DATA does not exist.
ERROR: File WORK.BLUE_CARS_TABLE does not exist.
66
67         run;

ERROR: Library NEW does not exist.

Can you please help me fix this issue?

Re: Output tables as Excel files

Linlin — Thu, 02 Aug 2012 18:08:46 GMT

Sorry, I can't fix the problem and have deleted my post. I don't know how to get the dataset name created by the hash output method.

you can try the code below if you want to:

data sasuser.test;
input entity $;
cards;
aaa
aaa
Aaa
bbb
BBB
ccc
;

proc sql noprint;
select distinct(upcase(entity)) into :list separated by ' '
    from sasuser.test;
quit;
%macro test;
%do i=1 %to %sysfunc(countw(&list));
%let dsn=%scan(&list,&i);
   proc sql;
    create table sasuser.&dsn as
     select * from sasuser.test
     where upcase(entity)="&dsn";
libname new "c:\temp\&dsn..xls";
   drop table new.&dsn;
   quit;
   data new.&dsn;
     set sasuser.&dsn;
   run;
libname new clear;
%end;
%mend;

%test

log file:

845 data sasuser.test;
846 input entity $;
847 cards;

NOTE: The data set SASUSER.TEST has 6 observations and 1 variables.
NOTE: DATA statement used (Total process time):
real time 0.03 seconds
cpu time 0.00 seconds

854 ;
855
856 proc sql noprint;
857    select distinct(upcase(entity)) into :list separated by ' '
858      from sasuser.test;
859 quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

860 %macro test;
861   %do i=1 %to %sysfunc(countw(&list));
862   %let dsn=%scan(&list,&i);
863     proc sql;
864      create table sasuser.&dsn as
865       select * from sasuser.test
866       where upcase(entity)="&dsn";
867   libname new "c:\temp\&dsn..xls";
868     drop table new.&dsn;
869     quit;
870     data new.&dsn;
871       set sasuser.&dsn;
872     run;
873    libname new clear;
874    %end;
875 %mend;
876
877   %test
NOTE: Table SASUSER.AAA created, with 3 rows and 1 columns.

NOTE: Libref NEW was successfully assigned as follows:
      Engine:        EXCEL
      Physical Name: c:\temp\AAA.xls
NOTE: Table NEW.AAA has been dropped.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.10 seconds
      cpu time            0.01 seconds

NOTE: There were 3 observations read from the data set SASUSER.AAA.
NOTE: The data set NEW.AAA has 3 observations and 1 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds

NOTE: Libref NEW has been deassigned.
NOTE: Table SASUSER.BBB created, with 2 rows and 1 columns.

NOTE: Libref NEW was successfully assigned as follows:
      Engine:        EXCEL
      Physical Name: c:\temp\BBB.xls
NOTE: Table NEW.BBB has been dropped.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.09 seconds
      cpu time            0.03 seconds

NOTE: There were 2 observations read from the data set SASUSER.BBB.
NOTE: The data set NEW.BBB has 2 observations and 1 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

NOTE: Libref NEW has been deassigned.
NOTE: Table SASUSER.CCC created, with 1 rows and 1 columns.

NOTE: Libref NEW was successfully assigned as follows:
      Engine:        EXCEL
      Physical Name: c:\temp\CCC.xls
NOTE: Table NEW.CCC has been dropped.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.09 seconds
      cpu time            0.04 seconds

Re: Output tables as Excel files

nicnad — Thu, 02 Aug 2012 19:01:38 GMT

Thank you for your reply.

I tried the following ODS statement

ODS HMTL FILE="C:\TEMP.XLS";

PROC PRINT DATA=work.TEST;

RUN;

ODS HTML CLOSE;

and I get the following error :

8          ODS _ALL_ CLOSE;
NOTE: Some of your options or statements may not be supported with the Activex or Java series of devices. Graph defaults for these
      drivers may be different from other SAS/GRAPH device drivers. For further information, please contact Technical Support.
9          OPTIONS DEV=ACTIVEX;
10         FILENAME EGHTML TEMP;
NOTE: Writing HTML(EGHTML) Body file: EGHTML
11         ODS HTML(ID=EGHTML) FILE=EGHTML ENCODING='utf-8' STYLE=EGDefault
11       ! STYLESHEET=(URL="file:///C:/Program%20Files/SAS/Shared%20Files/BIClientStyles/EGDefault.css")
11       ! ATTRIBUTES=("CODEBASE"="http://www2.sas.com/codebase/graph/v91/sasgraph.exe") NOGTITLE NOGFOOTNOTE GPATH=&sasworklocation
11       ! ;
12
13         %gaccessible;
WARNING 1-322: Assuming the symbol HTML was misspelled as HMTL.
NOTE: Writing HTML Body file: C:\TEMP.XLS
ERROR: Insufficient authorization to access /u01/app/sas/Config9.1.3/EMiner/Lev1/SASMain/C:\TEMP.XLS.
WARNING: No body file. HTML output will not be created.
14         ODS HMTL FILE="C:\TEMP.XLS";
               ____
               1
15         PROC PRINT DATA=work.TEST;
16         RUN;

NOTE: There were 373 observations read from the data set WORK.TEST.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.65 seconds
      cpu time            0.39 seconds

17         ODS HTML CLOSE;
18
19         %LET _CLIENTTASKLABEL=;
20         %LET _EGTASKLABEL=;
21         %LET _CLIENTPROJECTNAME=;
22         %LET _SASPROGRAMFILE=;
23
24         ;*';*";*/;quit;run;
25         ODS _ALL_ CLOSE;
26
27
28         QUIT; RUN;

I am running SAS entreprise Guide 4.1 and the export to excel functions works great, but I can't access the code that was submitted to make this exportation. Anyone know an easy way to export to XLS?

If so I think the easier would be to write a loop so that every datasets in WORK library would be exported to xls :

Delete all tables in work libray

...

Run my code that creates the sas tables in work library

...

For each dataset in work

export to .xls as "c:\" & datasetname & ".xls"

next dataset

end sub

Could someone please help me write a code that would achieve this?

Thank you for your help.

Re: Output tables as Excel files

Kfhansen — Thu, 02 Aug 2012 19:21:06 GMT

From the error you're getting I assume you are trying run code you wrote yourself?

When you run code like this pointing to a 'local' destination you need to make sure that your EG process is running on the local server as well and not on your server - SASApp or whatever your compute server is named.

Re: Output tables as Excel files

tish — Thu, 02 Aug 2012 19:58:12 GMT

Nicnad, perhaps we can go back to the original question and use a different technique for pulling entity information from one file into different files. If you go back to using macros, we can sandwich some ODS code around the different entities so you will create a bunch of files for Excel. The .xml files can all be read by more current versions of Excel.

proc sql noprint;

select

entity into :entity_list separated by ' '

from

sasuser.test;

quit;

%MACRO loop_through_entity_list;

%let entity_number = %eval(%sysfunc(count(&entity_list, %str( )))+1);

%do i = 1 to &entity_number;

%let this_entity = %scan(&entity_list, &i);

ods tagsets.ExcelXP

file="/something/something_else/output/output_&this_entity..xml"

options (embedded_titles='yes')

style=sansPrinter;

select *

from sasuser.test

where entity = &this_entity;

ods tagsets.ExcelXP close;

%end;

%MEND loop_through_entity_list;

proc sql;

%loop_through_entity_list;

quit;

This is a lot simpler to debug. Note that I'm an ODS lightweight, so perhaps someone can make this code better for you.

Hope this helps

Re: Output tables as Excel files

nicnad — Thu, 02 Aug 2012 20:07:27 GMT

Thank you tish.

This might be the right way to go.

I am getting an error at this line :

14 %MACRO loop_through_entity_list;

16 %let entity_number = %eval(%sysfunc(count(&entity_list, %str( )))+1);

18 %do i = 1 to &entity_number;

ERROR: Expected %TO not found in %DO statement. A dummy macro will be compiled.

Do you know how to fix it?

Thank you for your help and time.

Re: Output tables as Excel files

Kfhansen — Thu, 02 Aug 2012 20:09:27 GMT

"%do i=1 to ...... " should be "%do i=1 %to ... " thats all

Re: Output tables as Excel files

nicnad — Thu, 02 Aug 2012 20:33:52 GMT

Thank you for pointing this out Kfhansen.

I get the following errors :

Insufficient authorization to access /u01/app/sas/Config9.1.3/EMiner/Lev1/SASMain/c:\BLUE.xml.

Kfhansen pointed out that "When you run code like this pointing to a 'local' destination you need to make sure that your EG process is running on the local server as well and not on your server - SASApp or whatever your compute server is named." But I don't really know how to make EG run on my local server...

Also the entity variable contains values which are multiple words separated by space e.g. "blue car" and your macro variable only seems to pick up the first word.

Hope you will be able to help me with this.

Thank you.

Re: Output tables as Excel files

tish — Thu, 02 Aug 2012 21:00:10 GMT

I can address only the blanks in the entity variable... You can write out the data to a macro variable using separated by something other than a blank. I'd use a character that isn't in your list of entities. Then set up your entity_number to count the number of those characters. Finally, set up this_entity using the optional third parameter used by the %SCAN macro function... So if you use asterisks as your separator, then that assignment would look like

%let this_entity = %scan(%entity_list, &i, *);

Re: Output tables as Excel files

Kfhansen — Thu, 02 Aug 2012 21:14:28 GMT

at the top of your EG program editor you should see "Selected Server: .. " next to the grayed out stop menu .. click that and choose local to execute locally.

Also as tish suggested I would add a seperator in your first sql where you create the list

select .... into this_list seperated by ','

and change the %scan to %scan(&this_list,&x,",")

Re: Output tables as Excel files

Cynthia_sas — Thu, 02 Aug 2012 22:57:21 GMT

Hi:

In addition to all the other suggestions, related to your EG log, the log did tell you what was wrong. Here's the warning:

WARNING 1-322: Assuming the symbol HTML was misspelled as HMTL.

and here's the code you posted:

ODS HMTL FILE="C:\TEMP.XLS"; <--error in this line

PROC PRINT DATA=work.TEST;

RUN;

ODS HTML CLOSE;

Notice that you opened the HMTL destination, but you closed the HTML destination. There is no HMTL destination. Usually, when you use ODS, the opening invocation and the closing invocation have to match. Also you tried to "fool" EG by naming the file with a .XLS extension. There is a good chance that when you use this technique, you canNOT open the file in EG. EG will want to show you the HTML file, as HTML, because EG knows that it created an HTML file. Just because you named the file with a .XLS extension does not mean that EG will launch Excel for you. Sometimes, you have to go to Windows Explorer and right click on the file or you have to go inside Excel and do a File --> Open in order to open the file that you create with ODS.

In addition, Microsoft doesn't really not like the "flavor" of HTML tags created by ODS HTML. By default ODS HTML creates HTML 4.0 tags. In response to the HTML 4.0 specification of the W3C, Microsoft wrote their own "flavor" of HTML for Office 2000. ODS can create the Microsoft "flavor" of HTML, but you would have to use this destination instead of ODS HTML:

ODS MSOFFICE2K FILE="C:\TEMP_MSO.XLS"; <--use Microsoft "flavor" of HTML

PROC PRINT DATA=work.TEST;

RUN;

ODS MSOFFICE2K CLOSE; <-- Close what was opened

And, still, EG will probably not want to open Excel for you automatically. If you open the file with Notepad, you will see HTML tags. Both Excel and EG know that there are HTML tags in the file. EG ignores the .XLS extension and shows you the file on the result tab using a browser-like viewer. Excel will open the file, outside of EG; however, since you gave the file an XLS extension, Microsoft wants you to know, that it knows that you didn't really create a binary XLS file and so Excel will display a somewhat snarky message:

The file that you are trying to open "fileabc.xls" is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now.

(as described here: http://support.sas.com/kb/31/956.html) All you have to do is click Yes to get Excel to open the file and render the HTML inside Excel.

So, aside from any other issues, this is what was happening in EG when you 1) misspelled the destination and 2) used a file extension of .XLS; Excel started to "harden" file extension in Office 2007. In the old days of SAS and Excel, if you named your HTML file with a .XLS extension, Excel would launch without complaint (in Office 97, Office 2000 and Office 2003). You are never creating a "true, binary" Excel file with ODS -- you are only creating ASCII text files that Excel knows how to open. EG pays more attention to the destination you use (HTML) than to the file extension you give the file. That just means that if you had NOT misspelled the destination, you would have needed to go outside of EG to find and open the output file.

More information,

cynthia

Re: Output tables as Excel files

nicnad — Mon, 06 Aug 2012 14:38:35 GMT

Thank you for the precision Cynthia.

I still get an error when using the proper code :

NOTE: Some of your options or statements may not be supported with the Activex or Java series of devices. Graph defaults for these

drivers may be different from other SAS/GRAPH device drivers. For further information, please contact Technical Support.

9 OPTIONS DEV=ACTIVEX;

10 FILENAME EGHTML TEMP;

NOTE: Writing HTML(EGHTML) Body file: EGHTML

11 ODS HTML(ID=EGHTML) FILE=EGHTML ENCODING='utf-8' STYLE=EGDefault

11 ! STYLESHEET=(URL="file:///C:/Program%20Files/SAS/Shared%20Files/BIClientStyles/EGDefault.css")

11 ! ATTRIBUTES=("CODEBASE"="http://www2.sas.com/codebase/graph/v91/sasgraph.exe") NOGTITLE NOGFOOTNOTE GPATH=&sasworklocation

11 ! ;

13 %gaccessible;

NOTE: Writing MSOFFICE2K Body file: C:\TEMP_MSO.XLS

ERROR: Insufficient authorization to access /u01/app/sas/Config9.1.3/EMiner/Lev1/SASMain/C:\TEMP_MSO.XLS.

WARNING: No body file. MSOFFICE2K output will not be created.

14 ODS MSOFFICE2K FILE="C:\TEMP_MSO.XLS";

16 PROC PRINT DATA=work.NULL;

18 RUN;

Do you know what is causing this error and how to solve it?

Re: Output tables as Excel files

nicnad — Mon, 06 Aug 2012 14:40:09 GMT

Thank you for the reply.

I don't have the same interface as you, but when I go in the "code" menu and click choose a server, I don't have the choice to run it on local server.

Any idea how to fix this?

Thank you all for your help and time.

Re: Output tables as Excel files

Cynthia_sas — Mon, 06 Aug 2012 17:29:24 GMT

Hi:

Here are some other suggestions, but you might need to work with one of your colleagues who has more experience with SAS and/or work with Tech Support. The error message you got is very interesting. I don't have many solutions, just suggestions and questions. Solutions will have to come from Tech Support.

NOTE: Writing MSOFFICE2K Body file: C:\TEMP_MSO.XLS

ERROR: Insufficient authorization to access /u01/app/sas/Config9.1.3/EMiner/Lev1/SASMain/C:\TEMP_MSO.XLS.

WARNING: No body file. MSOFFICE2K output will not be created.

14 ODS MSOFFICE2K FILE="C:\TEMP_MSO.XLS";

1) the fact that you do not see a LOCAL choice for server tells me that SAS is not installed on your LOCAL computer -- you must have SAS only installed on a server. A complicating factor is: HOW is SAS installed:

--a) just a client/server install (with local EG and remote SAS on a server)

--b) as part of an Enterprise Miner install

--c) as part of a BI install

2) The ERROR message above (red highlighting mine tells me several different things:

--a. The Insufficiant authorization message tells me that your SAS server has a working directory defined for programs and you might not be allowed to write to that directory. Whether you believe this is correct or incorrect, operating system permissions are created by an admin inside your company. Someone at your company has set permissions in such a way that they do not expect you to or they do not want you to write to the directory on the SAS server. Or, you may have write permissions to that location (which I think is unlikely given the "Lev1" piece of the name), but, your FILE= combined with the default server directory generates an incorrect UNIX path -- read on.

--b. The fact that all the slashes go a different direction than the Windows slashes implies to me that your SAS is running on a UNIX flavor computer (or region or partition). And, since there are no UNIX boxes with a C: drive, you've probably generated an incorrect UNIX file name by putting that C:\ in the FILE= option. You might not get the same message if you did this:

ODS MSOFFICE2K FILE="TEMP_MSO.XLS";

BUT, your admin still may have locked down that physical directory. So, there's a good chance that this suggestion will NOT work.If you do not receive any error messages and you CAN write to /u01/app/sas/Config9.1.3/Eminer/Lev1/SASMain directory path, then you will probably have to naviagate to that directory or FTP from that directory to your local machine to look at the file. As far as I know, there is no Microsoft Office for UNIX, so you'll have to get that file to your machine to open it. If you let EG do the download to your local machine, it will probably come down as an HTML File and you probably do not want that.

--c. The fact that you can't write to /u01/app/sas/Config9.1.3/Eminer/Lev1/SASMain coupled with the fact that EMINER is in the path name AND the fact that LEV1 is in the path name implies that you are using either Enterprise Miner standalone on a server or are using Enterprise Miner in a BI configuration. Or you could have both Enterprise Guide and Enterprise Miner. Or you could have EM and EG and the BI Platform -- and that is even more uncertainty about how you are trying to create your output. (Although most of your log looks like EG log messages, so I figure that someone possibly configured your EG to go to an Enterprise MIner directory) -- I find this confusing and possibly in need of more research on your part, either with guidance from Tech Support or guidance from your SAS Admin.

3) Since your FILE= string is appended to some default path, there is a possible resolution for this issue -- to use an explicit PATH= option:

ODS MSOFFICE2K PATH="C:\TEMP\" FILE="TEMP_MSO.XLS";

4) The fact that you also see a first ODS statement (created by EG) tells me that you have HTML turned on as the result type. I would recommend turning it off
(either using Tools-->Options)

or by -ADDING-

ODS _ALL_ CLOSE;

at the very top of all your code -- to turn off the EG automatically created output and ONLY create your output. Leave the rest of your program the same.

At this point, you are close to exhausting the type of help that you can get on the Forum. It may be time to open a track with Tech Support for more personalized help. They can look at all your code, ask questions about your configuration and make some recommendations, based on what they figure out. In addition, if you have older versions of SAS, they can test your code in that earlier version of SAS.

cynthia