Help using Base SAS procedures

Output tables as Excel files

Reply
Regular Contributor
Posts: 186

Output tables as Excel files

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!

SAS Super FREQ
Posts: 8,868

Re: Output tables as Excel files

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

Regular Contributor
Posts: 186

Re: Output tables as Excel files

Posted in reply to Cynthia_sas

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.

PROC Star
Posts: 7,487

Re: Output tables as Excel files

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

Regular Contributor
Posts: 186

Re: Output tables as Excel files

Yes I think it is.

Regular Contributor
Posts: 186

Re: Output tables as Excel files

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?

Super Contributor
Posts: 1,636

Re: Output tables as Excel files

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


Regular Contributor
Posts: 186

Re: Output tables as Excel files

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.

Occasional Contributor
Posts: 15

Re: Output tables as Excel files

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.

Occasional Contributor
Posts: 15

Re: Output tables as Excel files

Just an alternative.

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

Contributor
Posts: 52

Re: Output tables as Excel files

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

Regular Contributor
Posts: 186

Re: Output tables as Excel files

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;

15        

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

17        

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.


Occasional Contributor
Posts: 15

Re: Output tables as Excel files

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


Regular Contributor
Posts: 186

Re: Output tables as Excel files

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.

Contributor
Posts: 52

Re: Output tables as Excel files

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, *);

Ask a Question
Discussion stats
  • 21 replies
  • 1514 views
  • 6 likes
  • 6 in conversation