BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
rakeshvvv
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
bnawrocki
Quartz | Level 8

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;
  

View solution in original post

3 REPLIES 3
Shmuel
Garnet | Level 18

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.

bnawrocki
Quartz | Level 8

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;
  
ballardw
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 2714 views
  • 1 like
  • 4 in conversation