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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

@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.

View solution in original post

7 REPLIES 7
Reeza
Super User

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
Obsidian | Level 7

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
Diamond | Level 26 RW9
Diamond | Level 26

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

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

@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
Obsidian | Level 7

Thank you everyone for your response.

ramchinna24
Obsidian | Level 7

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.

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 7 replies
  • 1352 views
  • 3 likes
  • 4 in conversation