topic Re: Hi.proc export giving additional quotes to quoted character fields. in SAS Programming

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

Puspita_1 — Sat, 04 May 2024 03:23:51 GMT

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.

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

Puspita_1 — Sat, 04 May 2024 03:47:45 GMT

Please reply if anyone came across this

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

Patrick — Sat, 04 May 2024 09:00:52 GMT

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.

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

Puspita_1 — Sat, 04 May 2024 10:29:55 GMT

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

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

ballardw — Sat, 04 May 2024 11:53:49 GMT

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-data-AKA-generate/ta-p/258712 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.

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

Puspita_1 — Sat, 04 May 2024 12:20:58 GMT

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;

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

Tom — Sat, 04 May 2024 13:34:50 GMT

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/927011/highlight/true#M364844

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

Puspita_1 — Sat, 04 May 2024 15:23:41 GMT

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

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

Puspita_1 — Sat, 04 May 2024 15:29:13 GMT

but yes, if you use DSD the space is not coming

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

Tom — Sat, 04 May 2024 16:58:04 GMT

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

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

Puspita_1 — Sat, 04 May 2024 18:14:52 GMT

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","",,

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

Tom — Sat, 04 May 2024 19:49:48 GMT

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?

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

Puspita_1 — Sat, 04 May 2024 20:11:01 GMT

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.

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

Puspita_1 — Sun, 05 May 2024 00:01:45 GMT

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

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

Tom — Sun, 05 May 2024 03:48:44 GMT

But you still haven't said why it makes any difference. Are you using some poorly designed internal program to read the CSV files? Can't you just fix that program? Or are you using some commercial program to read the CSV file? If so let us know so what program. Perhaps there is an option in that program to read a NORMAL csv file. Or if nothing else so we know to avoid using that program.

Does the time it takes to make the file matter? If not the just write each variable independently so that you can test if it is empty and write something different in those cases.

Example:

data _null_;
  set have;
  file csv dsd ;
  if missing(name) then put '"",' @; else put name ~ @;
  if missing(test_date) then put '"",' @; else put test_date ~ @;
  put score @;
  put ;
run;

Result:

"Bob","02APR2024",85
"Susan","03MAR2023",100
"Puspita","",

Note if the character variable is the last on the line then do not include the comma in the alternative text to be written.

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

Patrick — Sun, 05 May 2024 06:01:21 GMT

@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);

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

Puspita_1 — Mon, 13 May 2024 10:38:24 GMT

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

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

Tom — Mon, 13 May 2024 11:55:47 GMT

@Puspita_1 wrote:

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

What solution did you have in mind? Do you want the unneeded quotes around all DATE values also?

If you are trying to automate something then you can use the FMTINFO() to find the category of format that is attached to a variable. Then you could generate the extra quotes for both character and date variables, but not for other numeric variables.

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

Puspita_1 — Wed, 15 May 2024 14:23:27 GMT

While executing the macro the error comes like

WARNING: Apparent invocation of macro SYSFUNCT not resolved.
ERROR: Required operator not found in expression: ne 1
ERROR: The macro PERFEND will stop executing.

Specially for this macro.PERFEND.the macro is executing multiple times and at last this msg pops up.

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

ballardw — Wed, 15 May 2024 14:34:57 GMT

@Puspita_1 wrote:

While executing the macro the error comes like

WARNING: Apparent invocation of macro SYSFUNCT not resolved.
ERROR: Required operator not found in expression: ne 1
ERROR: The macro PERFEND will stop executing.

Specially for this macro.PERFEND.the macro is executing multiple times and at last this msg pops up.

That warning most likely means there is a typo of %SYSFUNCT instead of %SYSFUNC somewhere in the code.

Fix it.

The %syfunc did not execute and anything relying on the result will not have the expected input.