topic Re: CSV export file in New SAS User

CSV export file

deepakkailay — Mon, 25 Mar 2019 11:02:18 GMT

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

Re: CSV export file

Jagadishkatam — Mon, 25 Mar 2019 10:50:37 GMT

Please try the below code

proc export data=prog269_ctrl2 dbms=tab label outfile="1.txt" replace;
putnames=yes;
run;

Re: CSV export file

deepakkailay — Mon, 25 Mar 2019 10:57:47 GMT

thanks for your reply. It will not resolve the double quotes. I need the file to be have comma separated value and variables

Re: CSV export file

deepakkailay — Mon, 25 Mar 2019 10:59:52 GMT

I need the output in this format first line variables and second line values. example:

var1,var2,var3

111,aaa,vvv

put statement is writing to same line

deepakkailay — Mon, 25 Mar 2019 11:05:47 GMT

Hi,

i have used below code:

filename file_1 "location/1.txt;

data _null_;

set prior_ds;

file file_1 dlm=',';

put 'Source,val1,val2'
#2 (_all_)(:);
run;

it is writing the variable name i.e source,val1,val2 and the values to the same line .

can you please suggest how i can put values to second line.

Re: CSV export file

Jagadishkatam — Mon, 25 Mar 2019 11:08:20 GMT

proc export data=prog269_ctrl2 dbms=dlm label outfile="1.txt" replace;
delimiter=',';
putnames=yes;
run;

We can try the delimiter as above

Re: CSV export file

deepakkailay — Mon, 25 Mar 2019 11:11:35 GMT

I want the variables to be shown as label values. but it is showing the variables labels in double quotes. how can i remove it?

Re: CSV export file

Jagadishkatam — Mon, 25 Mar 2019 11:18:18 GMT

We need to mention the putnames=no;

proc export data=prog269_ctrl2 dbms=dlm label outfile="1.txt" replace;delimiter=',';
putnames=no;
run;

Re: CSV export file

deepakkailay — Mon, 25 Mar 2019 11:26:13 GMT

i am getting this message after using putname=no;

WARNING: The LABEL option will be ignored when PUTNAMES=NO.

also, it is creating file without variable names but i need the variable names.

Re: CSV export file

Jagadishkatam — Mon, 25 Mar 2019 11:49:56 GMT

This should work

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

run;

Re: put statement is writing to same line

RW9 — Mon, 25 Mar 2019 11:51:14 GMT

Use the code window - its the {i} above post area to post code:

filename file_1 "location/1.txt;

data _null_;  
  set prior_ds;
  file file_1 dlm=',';
  if _n_=1 then put 'Source,val1,val2';
  put (_all_);
run;

Re: CSV export file

RW9 — Mon, 25 Mar 2019 11:56:31 GMT

A CSV file will have double quotes in if the data contains the delimiter, consider:

Somevar,Somethingelse,A line with a comma, in,something else

If that is read in the "A line with a comma, in" actually looks like two variables as the comma delimiter appears in the string. To counteract this, SAS (and all other implementations such as Excel) will quote that string to look:

Somevar,Somethingelse,"A line with a comma, in",something else

So it is clear that the comma there is part of a text string not a delimiter. This is standard behaviour, and if your data contains the delimiter, then that should be how it is presented.

Re: put statement is writing to same line

deepakkailay — Mon, 25 Mar 2019 12:00:53 GMT

put (_all_) is erroring out. If i am using _all_ then it is giving results as var1=111 .

But again, the value and variables are showing in the same line in notepad.

but I need the values and variables in separate lines.

Re: CSV export file

deepakkailay — Mon, 25 Mar 2019 12:03:23 GMT

thanks,

but i need the output in this way:

first line variable list and second line value with comma separated. Is there a way to get this output?

example:

var1,var2,var3

111,aaa,vvv

Re: CSV export file

Jagadishkatam — Mon, 25 Mar 2019 12:05:46 GMT

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;

Re: CSV export file

RW9 — Mon, 25 Mar 2019 12:10:44 GMT

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.

Re: CSV export file

deepakkailay — Mon, 25 Mar 2019 13:15:15 GMT

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

Re: put statement is writing to same line

Kurt_Bremser — Mon, 25 Mar 2019 13:39:50 GMT

@deepakkailay wrote:

put (_all_) is erroring out. If i am using _all_ then it is giving results as var1=111 .

But again, the value and variables are showing in the same line in notepad.

but I need the values and variables in separate lines.

DO NOT use notepad for the display of such datafiles. Windows notepad can't handle non-Windows text files properly.

Use notepad++ instead.

UNIX uses a single LF character for line breaks, while notepad expects the Windows-typical CRLF combo.

Text files from SAS UE will be in UNIX format, unless you specify a termstr=CRLF option in the file statement.

Re: CSV export file

deepakkailay — Mon, 25 Mar 2019 13:45:47 GMT

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?

Re: CSV export file

Tom — Mon, 25 Mar 2019 14:38:33 GMT

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