DATA Step, Macro, Functions and more

Proc export

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 145
Accepted Solution

Proc export

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


Accepted Solutions
Solution
‎08-30-2016 03:53 PM
Contributor
Posts: 22

Re: Proc export

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


All Replies
Trusted Advisor
Posts: 1,554

Re: Proc export

Posted in reply to rakeshvvv

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.

Solution
‎08-30-2016 03:53 PM
Contributor
Posts: 22

Re: Proc export

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;
  
Super User
Posts: 11,343

Re: Proc export

Posted in reply to rakeshvvv

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

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