sas

How to remove white spaces from CSV output using SAS, where each value is enclosed in double quotes?

I am able to output into a CSV file using SAS (file statement) with each value enclosed in double quotes, but not able to remove the empty spaces for missing values. Following is the code with sample input:

    data input;
     STUDY="XYZ"; INDATE="03-DEC-2024"; INTIME=""; output;
     STUDY="XYZ"; INDATE="04-DEC-2024"; INTIME=""; output;
     STUDY="XYZ"; INDATE="05-DEC-2024"; INTIME=""; output;
     STUDY="XYZ"; INDATE="06-DEC-2024"; INTIME=""; output;
    run;
    
    data _null_;
    file "&outpath.\test.csv" dsd dlm=",";
    set input;
    if _n_=1 then put '"STUDY","INDATE","INTIME"';
    put (STUDY INDATE INTIME) (~);
    run;

Output I am getting:

enter image description here

Output I want:

enter image description here

Appreciate the help!

Solution

  • Don't add the ~ modifier when the value is missing.

    ...
  if missing(study) then put study @ ; else put study ~ @;
  if missing(indate) then put indate @ ; else put indate ~ @;
  if missing(intime) then put intime @ ; else put intime ~ @;
  put;
...

    That will cause it to just put nothing between the delimiters when the values are missing.

    study,indate,intime
"XYZ","03-DEC-2024",
,"04-DEC-2024","08:25:00"
"XYZ",,"13:12:00"
"XYZ","06-DEC-2024",

    Or do you really need the file to also have those extra (unneeded) quotes even when the value is missing? If so then perhaps skip the DSD option and add the quotes yourself. Note you will probably need to make sure the MISSING option is set to ' ' to avoid getting "." for missing numeric variables.

    data _null_;
  file csv dlm=',' ;
  if _n_=1 then put '"STUDY","INDATE","INTIME"';
  set input;
  length _val_ $200;
  _val_ = quote(trimn(vvalue(study)));
  put _val_ @;
  _val_ = quote(trimn(vvalue(indate)));
  put _val_ @;
  _val_ = quote(trimn(vvalue(intime)));
  put _val_ @;
  put;
run;