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

[Tom]

---

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

[Puspita_1]

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.

[ballardw]

---

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

[Puspita_1]

Hi This code is not included in the existing macro which is available readymade. So, you have changed the code and redesigned? If possible, could you please explain step by step what each step do

ex:

source2 stand for?Thank you 

[ballardw]

---

@Puspita_1 wrote:

Hi This code is not included in the existing macro which is available readymade. So, you have changed the code and redesigned? If possible, could you please explain step by step what each step do

ex:

source2 stand for?Thank you 

---

SOURCE2 is a system option that means that lines that typically appear in a file referenced with %include to use code in that file, will appear in the log.

Since you didn't post any actual code there is nothing to address "step by step".

[Puspita_1]

This is the actual code and the definition of the macro. Please explain the below step by step. 

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

[Tom]

The %MACRO and %MEND are the wrappers that indicate it is a macro definition.

The filename statement makes and empty file in a temporary location.

The data step makes sure it is empty.  Note this is not needed since the FILENAME statement already made a new empty temporary file.

The LIB and DS macro variables are needed because they will be used to query the SAS view SASHELP.VCOLUMN to find the metadata about the variables in the dataset.  The %SCAN() is used to pick of the libname and memname from the name passed in.  The WORK. is add so that the same %SCAN() calls will work for one level and two level dataset names.

The %IF %THEN %DO %END block will conditionally run the data step that will write a statement into the CODEGEN file that will write the header line only on the first observation (when _n_=1).

Then the data _NULL_ step will write the code that can write the data.  The generated code tests if the value is empty and adjusts how it write the value.   It generates different code depending on whether the variable is character or not.

Then the last data _NULL_ step will actually write the file.  The FILE statement says what (where) the file is being written. The SET statement what dataset is being read. And the %INCLUDE statement causes the code written by the previous step(s) into CODEGEN to become part of the data step.

Look at the CODEGEN file (you will see it in the SAS LOG with the + at the start of the log lines because of the SOURCE2 option in the next step) to see what code it wrote.  You will see that for each variable it checks if missing and writes executes a different PUT statement based on whether it is missing.  It generates different code for character variables (type='char' in the SASHELP.VCOLUMN view).

[Tom]

Note I find it is generally much easier (and faster) to use the output of PROC CONTENTS than use SASHELP.VCOLUMNS (it is especially much faster if have a lot of librefs defined).

It will also make it easier to use FMTINFO() to find if there are any DATE variables.

Let's make a macro called DSN2CSVQ (Q for the goofy quoting required).

%macro dsn2csvq(dsn,fname);
* Get content information and order by varnum ;
proc contents data=&dsn noprint
  out=contents(keep=libname memname varnum name type format label)
;
run;
proc sort data=contents; by varnum; run;

* Write header line with qutoes ;
data _null_;
  file &fname dsd ;
  set contents;
  put name ~ @ ;
run;

* Generate code to write data lines ;
filename code temp;
data _null_;
  file code;
  set contents end=eof;
  length nliteral $65 null $10;
  if eof then null='''""''';
  else null='''"",''';
  nliteral=nliteral(name);
  if type=2 or fmtinfo(format,'cat') in ('date','time','datetime') then do;
    put 'if missing(' nliteral ') then put ' null '@; else put ' nliteral '~ @;';
  end;
  else put 'put ' nliteral '@;' ;
  if eof then put 'put;';
run;

* Append data lines ;
data _null_;
  file &fname dsd mod ;
  set &dsn;
%include code / source2;
run;
%mend dsn2csvq;

Now let's make a sample dataset and try using it.

data have;
 set sashelp.class(obs=5);
 date=today();
 format date yymmdd10.;
 if _n_=1 then call missing(name);
 if _n_=2 then call missing(sex);
 if _n_=3 then call missing(age);
 if _n_=4 then call missing(date);
run;
filename csv temp;
%dsn2csvq(dsn=have,fname=csv);

You can see in the log that it generate and ran this code to write the data lines:

53  +if missing(Name ) then put '"",' @; else put Name ~ @;
54  +if missing(Sex ) then put '"",' @; else put Sex ~ @;
55  +put Age @;
56  +put Height @;
57  +put Weight @;
58  +if missing(date ) then put '""' @; else put date ~ @;
59  +put;

And here the resulting CSV file.

"Name","Sex","Age","Height","Weight","date"
"","M",14,69,112.5,"2024-05-18"
"Alice","",13,56.5,84,"2024-05-18"
"Barbara","F",,65.3,98,"2024-05-18"
"Carol","F",14,62.8,102.5,""
"Henry","M",14,63.5,102.5,"2024-05-18"

[Tom]

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.

[Puspita_1]

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