Exporting CSV File Using dsd option and Getting rid of QUOTATION MARKS for Missing Values

Accepted Solution Solved
Reply
Contributor
Posts: 38
Accepted Solution

Exporting CSV File Using dsd option and Getting rid of QUOTATION MARKS for Missing Values

Hallo,

I want to export a SAS dataset as a CSV file in the following format.

"email","Portal","Id"

"J_ALTERMANN@GMX.DE","14;16","2"

"JORDI_LLINAS@DBSCHENKER.COM",,"3"

"MARCELHLIG@WEB.DE",,"3"

"EHSANULLAH@YAHOO.DE","17","4"

I want to enclosed each value in quotation marks except the missing values. Please note that in lines 3 and 4, the values for Portal are missing, so they are exported without quotation marks inbetween two successive comma's without any SPACE.

I have a SAS dataset called "Test_data" as follows:

email
Portal
Id
J_ALTERMANN@GMX.DE14;162
JORDI_LLINAS@DBSCHENKER.COM3
MARCELHLIG@WEB.DE3
EHSANULLAH@YAHOO.DE174

 

For this purpose I wrote the following SAS Code.

filename aus "D:\SAS_Projects\AbgleichGegenBlacklisten_140103\Test_outputfile.csv";

data _null_;

   length text1 $2000;

   length text2 $2000;

   length text3 $2000;

   set Test_data;

   file aus recfm = v lrecl = 10000 delimiter=',' dsd encoding = 'utf8';

    if _n_ = 1 then do;

         text1 = 'email';

         text2 = 'Portal';

         text3 = 'Id';

         put text1 ~ text2 ~ text3 ~;

    end;

   put email ~ Portal ~ Id ~;

run;

But this code gives me the CSV file in the following format:

"email","Portal","Id"

"J_ALTERMANN@GMX.DE","14;16","2"

"JORDI_LLINAS@DBSCHENKER.COM"," ","3"

"MARCELHLIG@WEB.DE"," ","3"

"EHSANULLAH@YAHOO.DE","17","4"

Please note that in lines 3 and 4, the values for Portal are missing, But they are exported with QUOTATION MARKS and a SPACE inbetween two successive comma's.

My Question is: How can I get rid of QUOTATION MARKS and a SPACE only for missing values ?

I shall be thankful for help.

Regards

Dr. Ehsan Ullah


Accepted Solutions
Solution
‎06-17-2014 09:56 AM
Super User
Super User
Posts: 7,942

Re: Exporting CSV File Using dsd option and Getting rid of QUOTATION MARKS for Missing Values

Posted in reply to ehsanmath

Ok, small change around:

data _null_;
  set sashelp.class;
  /* change for test */
  if name="Carol" then name="";
  length myline $2000.;

  file "s:\temp\rob\class.csv";
  if _n_=1 then do;
    put 'name,sex,age,height,weight';
  end;

  if name ne "" then myline='"'||strip(name)||'",'; else myline=',';
  if sex ne "" then myline=strip(myline)||'"'||strip(sex)||'",'; else myline=strip(myline)||',';
  if age ne "" then myline=strip(myline)||'"'||strip(age)||'",'; else myline=strip(myline)||',';
  if height ne "" then myline=strip(myline)||'"'||strip(height)||'",'; else myline=strip(myline)||',';
  if weight ne "" then myline=strip(myline)||'"'||strip(weight)||'"'; else myline=strip(myline)||'';
  put myline;
run;

View solution in original post


All Replies
Super User
Super User
Posts: 7,942

Re: Exporting CSV File Using dsd option and Getting rid of QUOTATION MARKS for Missing Values

Posted in reply to ehsanmath

Could you not drop the delimiter part of the output file and write it manually e.g:

data _null_;
  set sashelp.class;
 
  /* change for test */
  if name="Carol" then name="";

  file "s:\temp\rob\class.csv";
  if _n_=1 then do;
    put 'name,sex,age,height,weight';
  end;
  if name ne "" then put '"' name '",' @@; else put ',' @@;
  if sex ne "" then put '"' sex '",' @@; else put ',' @@;
  if age ne "" then put '"' age '",' @@; else put ',' @@;
  if height ne "" then put '"' height '",' @@; else put ',' @@;
  if weight ne "" then put '"' weight '"'; else put;
run;

Contributor
Posts: 38

Re: Exporting CSV File Using dsd option and Getting rid of QUOTATION MARKS for Missing Values

Hallo,

sounds to be a very good idea. I am getting CSV in the following format:

name,sex,age,height,weight

"Alfred ","M ","14 ","69 ","112.5 "

"Alice ","F ","13 ","56.5 ","84 "

"Barbara ","F ","13 ","65.3 ","98 "

,"F ","14 ","62.8 ","102.5 "

If you notice there is a SPACE after the value of each variable. For example, there is a SPACE after 'd' in the value "Alfred " for variable name. Can you please suggest ? How to get rid of this SPACE ?

Regards

Ehsan

Solution
‎06-17-2014 09:56 AM
Super User
Super User
Posts: 7,942

Re: Exporting CSV File Using dsd option and Getting rid of QUOTATION MARKS for Missing Values

Posted in reply to ehsanmath

Ok, small change around:

data _null_;
  set sashelp.class;
  /* change for test */
  if name="Carol" then name="";
  length myline $2000.;

  file "s:\temp\rob\class.csv";
  if _n_=1 then do;
    put 'name,sex,age,height,weight';
  end;

  if name ne "" then myline='"'||strip(name)||'",'; else myline=',';
  if sex ne "" then myline=strip(myline)||'"'||strip(sex)||'",'; else myline=strip(myline)||',';
  if age ne "" then myline=strip(myline)||'"'||strip(age)||'",'; else myline=strip(myline)||',';
  if height ne "" then myline=strip(myline)||'"'||strip(height)||'",'; else myline=strip(myline)||',';
  if weight ne "" then myline=strip(myline)||'"'||strip(weight)||'"'; else myline=strip(myline)||'';
  put myline;
run;

Contributor
Posts: 38

Re: Exporting CSV File Using dsd option and Getting rid of QUOTATION MARKS for Missing Values

thank you very much.

cheers

Super User
Super User
Posts: 7,039

Re: Exporting CSV File Using dsd option and Getting rid of QUOTATION MARKS for Missing Values

Posted in reply to ehsanmath

You can modify the _FILE_ variable to remove the " " and "." values.

* Make some test data ;

data test;

set sashelp.class (obs=2);

if _N_=1 then name=' ';

if _N_=2 then age=.;

run;

filename aus temp;

data _null_;

  set test;

  file aus lrecl=10000 dsd ;

  if _n_=1 then link names;

  put (_all_) (~) @;

  _file_=transtrn(_file_,'" "',trimn(' '));

  _file_=transtrn(_file_,'"."',trimn(' '));

  put;

return;

names:

  length _name_ $32;

  do while(1);

    call vnext(_name_);

    if upcase(_name_) eq '_NAME_' then leave;

    put _name_ ~ @;

  end;

  put;

  return;

run;

Super User
Posts: 10,023

Re: Exporting CSV File Using dsd option and Getting rid of QUOTATION MARKS for Missing Values

Posted in reply to ehsanmath
* Make some test data ;
data test;
set sashelp.class (obs=2);
if _N_=1 then name=' ';
if _N_=2 then age=.;
run;
filename aus 'c:\temp\x.csv';
filename x temp;
options missing=' ';
data _null_;
  set test;
  file x lrecl=10000 dsd ;
  put (_all_) (~) ;
run;
data _null_;
 infile x;
 file aus;
 input;
 _infile_=prxchange('s/" "//',-1,_infile_);
 put _infile_;
run;

or another interesting way .

data test;
set sashelp.class (obs=2);
if _N_=1 then name=' ';
if _N_=2 then age=.;
run;
filename aus temp;
filename x 'c:\temp\want.csv';
options missing=' ';
ods csv file=aus options(delimiter=',' Quoted_columns="123456789");
proc print data=test noobs;run;
ods csv close;
data _null_;
 file x;
 infile aus;
 input;
 _infile_=prxchange('s/""//',-1,_infile_);
 put _infile_;
run;


Xia Keshan

Contributor
Posts: 38

Re: Exporting CSV File Using dsd option and Getting rid of QUOTATION MARKS for Missing Values

This was my first post in this forum. I am suprised to see, there are a lots of SAS experts here.

Thank you very much to all of you for sharing ideas

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 974 views
  • 9 likes
  • 4 in conversation