topic Re: Proc export in SAS Programming

Proc export

rakeshvvv — Tue, 30 Aug 2016 18:02:09 GMT

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

Re: Proc export

Shmuel — Tue, 30 Aug 2016 18:52:41 GMT

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.

Re: Proc export

bnawrocki — Tue, 30 Aug 2016 19:49:10 GMT

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;

Re: Proc export

ballardw — Tue, 30 Aug 2016 19:55:19 GMT

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.