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!
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
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.
Is SAS/Access for PC File Formats included in your SAS license?
Yes I think it is.
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?
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
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.
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.
Just an alternative.
You could also use the SAS Add-in for MS-Office.
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
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.
"%do i=1 to ...... " should be "%do i=1 %to ... " thats all
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.
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, *);
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.