Hi.proc export giving additional quotes to quoted character fields.

[Puspita_1]

Proc export giving additional quotes to the quoted char values.and if u use data step for that it is giving a space or . To numeric values which is not required . Needs to change nothing.now it is , , but want ,, no space no .for missing numeric value.

[Puspita_1]

Please reply if anyone came across this

[Patrick]

Please provide a fully working code sample (sample data included) that allows us to replicate what you describe (=run in our environment) and explain/show the desired result.

[Puspita_1]

On Sat, 4 May 2024 at 3:57 PM, Puspita Padhi
wrote:

[ballardw]

As a bare minimum share the code that generated the output.

Better to show the log with the code and any notes or messages.

Best is to include example data, before export an after export.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the </> icon or attached as text to show exactly what you have and that we can test code against.

Note that depending on the file name created the tools that open the output may change the appearance of values. Example CSV files opened in spreadsheet software make many assumptions about what is "intended" with some unusual output.

[Puspita_1]

Hi 

Below is the output from proc export.

here extra quotes are coming.for character only one set of double quotes needed.

when you do this with datastep for numeric columns having missing value is diplaying . so to change that converted blank .but needed is ,, not , ,.

for numeric columns if it is missing value it should be ,, now it is , ,.

filename TRAI 'exe.csv';
PROC EXPORT data = work.ext_accounts
            OUTFILE =TRAI
               dbms=csv
               replace;
QUIT;

proc sql;
select name into :namelist separated by ',' 
from dictionary.columns
where upcase(libname)="WORK" and upcase(memname)="EXT_ACCOUNTS";
quit;
%put "&namelist.";

options missing='';

option noquotelenmax;
data _null_;
set ext_accounts;
original_balance=strip(original_balance);
file  "E:\exe.csv" dlm=',';
if _n_=1 then put "&namelist.";
put (_all_)(+0);
run;

[Tom]

The photograph you shared of the PROC EXPORT result looks correct to me.  If the value being written to the CSV file has a comma in it then the value needs be quoted so the file can be parsed.  So quotes around a value are NOT part of the value.  So any value actually does contain a quote that value needs to be quoted also so that the quotes are not removed when the CSV file is read back in.

The reason the photography you shared of the second text file has spaces for empty values is because you did NOT use the DSD option on the FILE statement.

If the values of your character variables have quotes around them that you do not want to write into the CSV file then perhaps you should first remove those?  You can use the DEQUOTE() function to do that.

If for some reason you want to add extra (ie unneeded) quotes around the values of some variables then use the ~ modifier in the PUT statement for those variables.  See this other recent thread:  https://communities.sas.com/t5/SAS-Programming/How-to-keep-char-format-when-export-data-to-csv/m-p/9...

[Puspita_1]

hi for the 2nd statement if I use dsd then again 3 quotes are coming for data step.

what is the other way not to display the space

[Tom]

---

@Puspita_1 wrote:

hi for the 2nd statement if I use dsd then again 3 quotes are coming for data step.

what is the other way not to display the space

---

Why did you add quotes around the values if you did not want them to be part of the value?

Share an example of your data to get help writing a CSV file from it.  It does not need to be your real data, just enough to demonstrate the issue.  To share your data please post TEXT of the data.  Preferable as a data step that creates the data.  Make sure to use the Insert SAS Code button in the menu bar so you get a pop-up window to paste/edit the text.

Something like:

data have;
  infile cards dsd truncover;
  input name :$30. test_date :date. score ;
  format test_date date9.;
cards;
Bob,02APR2024,85
Susan,03MAR2023,100
;

So a normal CSV file created from that dataset would look like this:

name,test_date,score
Bob,02APR2024,85
Susan,03MAR2023,100

What do you want that is different than that?  Why do you want something different? Are you transferring the data to some other software? What software? What options did you use to read the CSV file?

[Puspita_1]

so for the above data set i want like this character data should be quoted  "" and numeric no quotes.

for missing value for character "" and for numeric ,, no space no dots.Thank you

name,test_date,score
"Bob","02APR2024",85
"Susan","03MAR2023",100
"Puspita","",,

[Tom]

But WHY???  What is the point of adding the quotes that are not necessary?

Lets read in your example as two character and one numeric variable.  The code might look like:

data have;
  infile 'myfile.csv' dsd truncover firstobs=2;
  input name :$10. test_date :$9. score ;
run;

Result:

Obs    name       test_date    score

 1     Bob        02APR2024      85
 2     Susan      03MAR2023     100
 3     Puspita                    .

If we write that as a CSV file but add the ~ modifier after the character variables.

filename csv temp;
proc transpose data=have(obs=0) out=names;
  var _all_;
run;

data _null_;
  file csv dsd ;
  set names;
  put _name_ @ ;
run;

data _null_;
  file csv dsd mod ;
  set have;
  put name ~ test_date ~ score;
run;

We get

name,test_date,score
"Bob","02APR2024",85
"Susan","03MAR2023",100
"Puspita"," ",

The only difference seems to be that empty strings are represents as a quoted single space.  But since there isn't really any different between a string that is only spaces and an empty one (definitely in SAS and also it most other normal applications) then that should be good enough.

You could try using ODS CSV.

ods csv file=csv;
options missing=' ';
proc print data=have noobs;
run;
options missing='.';
ods csv close;

That will add quotes around the character variables.  It will also represent the missing character value as a null string instead either a quoted space or two quotes with nothing between them.

"name","test_date","score"
"Bob","02APR2024",85
"Susan","03MAR2023",100
"Puspita",,

Again what is the difference between a empty value and a value that is two quotes with nothing between them?

[Puspita_1]

Agree!!I am also getting the result what you have given like " " but the requirement is if its character then "" and if its numeric ,, Now space is coming for characters i mean " ".

the only difference is to distinguish which one is char and which one is numeric by seeing the file.

if it is ,, for all missing then we don't know the data types but if we give quotes then it is char missing.

The only problem is now is " "; How to remove this space while using ~modifier.

if i use if then to remove the blank space inside the quote, then it is not giving me the quotes.

[Puspita_1]

Hi Is that possible to remove the space by using any other approach.

[Patrick]

---

@Puspita_1 wrote:

Hi Is that possible to remove the space by using any other approach.

---

Your required csv format is a bit unusual which is why I assume there isn't anything ready-made available. Using SAS you can always write your own code to exactly get what you want.

data work.class;
  length row_num 8;
  set sashelp.class;
  if _n_=2 then call missing(of _all_);
  if _n_=3 then call missing(name, height);
  format height 16.4;
  row_num=_n_;
run;

%macro MyDs2csv(
  inds=,
  outfile=,
  header=Y
  );
  filename codegen temp;
  data _null_;
    stop;
    file codegen;
  run;

  %local lib ds;
  %let lib=%upcase(%scan(work.&inds,-2));
  %let ds =%upcase(%scan(work.&inds,-1));

  /* generate code */

  %if %sysfunc(substr(%upcase(&header),1,1))=Y %then
    %do;
      data _null_;
        file codegen mod;
        set sashelp.vcolumn(where=(libname="&lib" and memname="&ds")) end=last;
        if _n_=1 then put 'if _n_=1 then do;';
        put "put '" name @;
        if not last then put +(-1) ",' @;" ;
        else put +(-1) "';" / "end;";
      run;
    %end;

  data _null_;
    file codegen mod;
    set sashelp.vcolumn(where=(libname="&lib" and memname="&ds")) end=last;
    retain hold '@';
    if type='char' then
      do;
        put 'if not missing(' name +(-1) ') then put ''"'' ' name '+(-1) ''"'' ' hold ';' 'else put ''""'' ' hold ';' ;
      end;
    else
      do;
        put 'if not missing(' name +(-1) ') then put ' name '+(-1) ' hold ';' ;
      end;
    if not last then put "put ',' @;";
    else put "put;";
  run;

  /* execute generated code */
  data _null_;
    file "&outfile";
    set &inds;
    %include codegen /source2;
  run;
  filename codegen clear;
%mend;

%MyDs2csv(inds=work.class,outfile=c:\temp\test.csv);

[Puspita_1]

this is when type=char but this is not applying when date is ddmmyy10. format and character date fileld.what will be the solution for this