Dear All,
I want to export two sas dataset into one excel with one tab for each dataset. Can some one help me on this. I am using the following code.
PROC EXPORT DATA= WORK.ae
OUTFILE= "c\ae.csv"
DBMS=CSV REPLACE;
PUTNAMES=Yes;
RUN;
PROC EXPORT DATA= WORK.cm
DBMS=CSV REPLACE;
PUTNAMES=Yes;
RUN;
I would need the datasets in the same excel.
Also I would like to have collumn label instead of collumn name and bolded to any colur.
Thanks in advance
You should add a SHEET statement to specify the name of the Excel sheet (tab) you want the dataset to copy to. For example:
data x;
input Name $ Age;
datalines;
Sue 25
Tom 18
;
data y;
input State $ Capital $;
datalines;
Ohio Columbus
Georgia Atlanta
;
proc export
data=x
file="C:\temp\myExcel.xlsx"
dbms=xlsx replace;
sheet="Names";
run;
proc export
data=y
file="C:\temp\myExcel.xlsx"
dbms=xlsx replace;
sheet="States";
run;
You can concatenate the two tables before exporting:
data both;
set ae cm;
run;
proc export data=both;
OUTFILE= "c:\ae_and_cm.csv"
DBMS=CSV REPLACE;
PUTNAMES=Yes;
RUN;
Assuming both tables have same variables, otherwise it does not make sense to me.
You should add a SHEET statement to specify the name of the Excel sheet (tab) you want the dataset to copy to. For example:
data x;
input Name $ Age;
datalines;
Sue 25
Tom 18
;
data y;
input State $ Capital $;
datalines;
Ohio Columbus
Georgia Atlanta
;
proc export
data=x
file="C:\temp\myExcel.xlsx"
dbms=xlsx replace;
sheet="Names";
run;
proc export
data=y
file="C:\temp\myExcel.xlsx"
dbms=xlsx replace;
sheet="States";
run;
CSV files are not Excel files with tabs. Each CSV is a text file. CSV does not support appearnce items such as font, font characteristics or colors.
You might get what you want by printing to a format that does support such things. One method is to print to ODS tagsets.excelxp:
ods tagsets.excelxp file="C:\path\file.xls";
proc print data=Work.Ae noobs label;
run;
Proc print data=work.cm noobs label;
run;
ods tagsets.excelxp close;
Creates a file, actually in XML format but Excel will open it. By default each proc output should be on a separate tab.
The ODS style in effect will control appearance. You may specify a different style as part of the ODS tagsets.Excelxp statement.
Proc Print also provides options to adjust some items appearance though getting Excel to honor them isn't always trivial.
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.