Exporting file in Excel through file statement.

[ramchinna24]

Hi,

It might be simple solution. unfortunately, I coudn't get proper answer anywhere in google.

I know other methods to send file to external location. But specifically I am looking for following method.

I am trying to send file in excel using file statement using the following code.

data _null_;
set sashelp.class;
file "/r00/home/rkava001/test.xls";
put name age sex;
run;

*text file;

data _null_;
set sashelp.class;
file "/r00/home/rkava001/test.dat";
put name age sex;
run;

The issue is, the output file is showing name age sex columns as one column in excel. But I want name age sex in separate columns.

can any any one please help?

In text file it showiing all observations are coming in one row. I need them in seperate lines

Cheers,

Ramu

[Reeza]

Excel is proprietary file type and not a simple text file so usually DDE is used for these types of cases. 

One way to force separate columns in DDE is to use a tab character ('09'x) to put between the variables. I can't really recommend this as solution since its not supported and not really supposed to work. That it does is kind of a fluke and you will get an error for sure when you open the file with Excel. 

Adding the tab seems to work though - tested on SAS UE with a Mac.

ODS gives you more control and a native xlsx file.

ods excel file='/folders/myfolders/demo.xlsx';

proc print data=sashelp.class noobs label;
var name age sex;
run;

ods excel close;

---

@ramchinna24 wrote:

Hi,

It might be simple solution. unfortunately, I coudn't get proper answer anywhere in google.

I know other methods to send file to external location. But specifically I am looking for following method.

I am trying to send file in excel using file statement using the following code.

data _null_;
set sashelp.class;
file "/r00/home/rkava001/test.xls";
put name age sex;
run;

*text file;

data _null_;
set sashelp.class;
file "/r00/home/rkava001/test.dat";
put name age sex;
run;

The issue is, the output file is showing name age sex columns as one column in excel. But I want name age sex in separate columns.

can any any one please help?

In text file it showiing all observations are coming in one row. I need them in seperate lines

Cheers,

Ramu

---

[ramchinna24]

Hi Reeza,

Thank you for your response. yes somehow it is working.

data _null_;
set sashelp.class;
file "/r00/home/rkava003/test.xls";
put name '09'x age '09'x sex;
run;

But I am not getting the column names.

for example if it is CSV then I can use following method but in excel, how to get even column names?

data _null_;
set sashelp.class;
file "/r00/home/rkava003/test.csv";

if _n_=1 then put "Name,Age,Sex";
put name ',' age ',' sex;
run;

??

[RW9]

Use one of the tools specifically designed to write to Excel - 

proc export

ods tagsets.excelxp

ods excel

libname excel

There are thousands of examples out there, each has its own strengths and weaknesses, ods excel is the most recent and creates native xlsx files, so that would be my recommendation at the moment.

With regards to CSV, this is a plain text comma separated file.  Excel can read this file, and split the data out into columns, however the file contains no other information about formatting, so Excel does nothing further.  This is however the best format for transferring data across systems, but not the best for displaying in Excel.  

So, it depends on what you want to do with it.

[Kurt_Bremser]

This code:

data _null_;
set sashelp.class;
file "$HOME/sascommunity/test.csv" dlm=',';
if _n_ = 1 then put "Name,Age,Sex";
put name age sex;
run;

delivers a text file that has the column names in row 1. When I open that in Excel 2013, the column names appear in row 1. Since the idiotic Excel does not get it on its own what a csv file is, you have to split the text into columns, though.

LibreOffice enters the correct import wizard on its own when double-clicking the csv, and gets it right from the start.

[Reeza · Accepted solution]

---

@ramchinna24 wrote:

Hi Reeza,

Thank you for your response. yes somehow it is working.

data _null_;
set sashelp.class;
file "/r00/home/rkava003/test.xls";
put name '09'x age '09'x sex;
run;

But I am not getting the column names.

for example if it is CSV then I can use following method but in excel, how to get even column names?

data _null_;
set sashelp.class;
file "/r00/home/rkava003/test.csv";

if _n_=1 then put "Name,Age,Sex";
put name ',' age ',' sex;
run;

??

---

There's no tabs in either of those, which is the approach I said did work. And that's CSV not XLS so the question has now changed when you said you didn't want a different option? If so, let us know so we can offer better options.

[ramchinna24]

Thank you everyone for your response.

[ramchinna24]

data _null_;
set sashelp.class;
file "/r00/home/rkava003/test.xls";
IF _N_=1 THEN PUT "NAME" '09'x "AGE" '09'x "SEX";
put name '09'x age '09'x sex;
run;

IT WORKED NOW EVEN FOR VARIABLE NAMES.