CSV export file

[Jagadishkatam]

Please check the below example , the same code is generating the expected output.

Could you please check how your data is different from sashelp.class

proc export data=sashelp.class dbms=dlm  outfile="1.txt" replace;
delimiter=',';
run;

[deepakkailay]

I need to use the label option with proc export. But if i use label option then it is showing variables in double quotes.

[RW9]

Yes, simply:

filename file_1 "&_sasws_./1.txt";

data _null_;  
  set sashelp.class;
  file file_1 dlm=',';
  if _n_=1 then put 'name,sex,age,height,weight';
  put name sex age height weight;
run;

filename file_1 clear;

Note I use a defaut dataset here, however it will work fine with yours, just update it, and do bear in mind that your code presented had errors - filename missing quote for instance.

Should work fine, though be aware that if your data contains the delimiter it will be quoted - this is a must, otherwise the datafile will be wrong and not usable as previously mentioned.

[deepakkailay]

thanks.

When I am opening the file in notepad++ or in worddoc, then it is opening in two lines.

But If I am opening in notepad then it is showing in one line. Why is it so?

[Tom]

That is a bug in PROC EXPORT's internal code.  You cannot change how it works.

Fortunately to generate a CSV file you don't need to use PROC EXPORT. You can do it with a simple data step.

To just write the data without a header row use something like this.

filename csv 'myfile.csv' termstr=crlf;
data _null_;
  set sashelp.class ;
  file csv dsd ;
  put (_all_) (+0);
run;

Here is method that uses PROC TRANSPOSE and an extra data step to write the header row.  Make sure to use the MOD option on the FILE statement when appending the data rows.  Note that if the dataset does not have labels then the _LABEL_ variable will not be generated by PROC TRANSPOSE.  Also if the variable does not have a label then the _LABEL_ variable will be empty.

filename csv 'myfile.csv' termstr=crlf;
proc transpose data=sashelp.class(obs=0) out=names;
  var _all_;
run;
data _null_;
  file csv dsd;
  length _name_ $32 _label_ $256;
  set names end=eof;
  _label_=coalescec(_label_,_name_);
  put _label_ @;
  if eof then put;
run;
data _null_;
  file csv dsd mod;
  set sashelp.class ;
  put (_all_) (+0);
run;

Also note that you could just post-process the file that PROC EXPORT generate and let SAS remove any unneeded quotes. (Quotes needed to make sure lines can be parsed will be re-generated.)

filename csv temp;
proc export data=sashelp.class dbms=csv
  outfile=csv replace
  label
;
run;
data _null_;
  infile csv obs=2;
  input ;
  put _infile_;
run;
filename newcsv temp;
data _null_;
  infile csv dsd truncover length=len column=col ;
  file newcsv dsd ;
  do until(col>len);
    input val :$32767. @;
    put val @;
  end;
  put;
run;
data _null_;
  infile newcsv obs=2;
  input ;
  put _infile_;
run;

Generated by LABEL option in PROC EXPORT:

"Name","Sex","Age","Height","Weight"
Alfred,M,14,69,112.5

Result of post-processing step:

Name,Sex,Age,Height,Weight
Alfred,M,14,69,112.5

[Kurt_Bremser]

Why not draw the header line from dictionary.columns?

proc sql noprint;
select trim(name) into :headerline separated by ','
from dictionary.columns
where libname = 'SASHELP' and memname = 'CLASS';
quit;

filename csv 'myfile.csv' termstr=crlf;

data _null_;
set sashelp.class;
file csv dsd;
if _n_ = 1 then put "&headerline.";
put (_all_) (+0);
run;

[Tom]

@Kurt_Bremser 

1) The number of names/labels could be more than can fit into a macro variable.

2) You would need to modify your SELECT statement to replicate the logic PUT uses to protect values with embedded delimiters and quotes.

3) DICTIONARY.COLUMNS can be EXTREMELY slow if you have external databases linked.   EVEN when care is taken in using a where clause to limit to a single libname and/or memname.

PS  You need to move the SET statement after the IF statement in your program to handle empty datasets.

[ballardw]

---

@deepakkailay wrote:

hi,

I am exporting one csv file with text extension. 

exmple: 

proc export data=prog dbms=csv label outfile="1.txt" replace;
run;

but, I am getting variables and values with double quotes. Can we remove these double quotes?

I have also tried one way

filename file_1 "/location/12.txt";

data _null_;

set program;

file file_1 dlm=',';
put 'var1,var2,var3'
#2 (_all_)(:);
run;

but in this case, when i am opening the file, it is appearing in one line in notepad. i want the file top be show variables in first line and values in second line. can you please provide some guidance?

I have also used  ods csv file="//location/1.txt";
ods csv options(sheet_name="tes" flow='tables");
proc print data=programnoobs;
run;
ods csv close;

But again, variable and values are appearing with double quotation.

I need the output in below format. first line variable and second line values.

example:

var1,var2,var3

111,aaa,vvv

---

You should look very closely at those values enclosed in quotes. Do any of them have a comma within the quotes? If so, then you do not want to remove the quotes because they are keeping the alignment for variables.

Example if your CSV exported file looks like:

var1, var2, var3
ABC Co.,"Smith, John",1234.55

Then removing the quotes around "Smith, John" would make John the value for VAR3 as that is what would be following the second comma.