Help using Base SAS procedures

Bouble qotes for numeric column in csv

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 87
Accepted Solution

Bouble qotes for numeric column in csv

I am trying to generate .csv file from sas data set with " as text delimiter and , as delimiter

i am getting output like this

63586900,"15/03/2012",4568012145,"Kumar"

1st column is numeric and i want quotes for that column and where as date is taking as character i want

date as numeric

is there any way to output like this

"63586900",15/03/2012,"4568012145","Kumar"


Accepted Solutions
Solution
‎03-23-2012 03:04 AM
Super User
Posts: 10,028

Re: Bouble qotes for numeric column in csv

You need a sepcial operator '~'  with option dsd.

data x;
 input a b : ddmmyy10. c d$; 
 format b ddmmyy10.;
cards;
63586900 15/03/2012 4568012145 Kumar 
;
run;
data _null_;
file 'c:\x.csv' dlm=',' dsd;
set x;
put a ~ b c ~ d ~  ;
run;


Ksharp

Message was edited by: xia keshan

View solution in original post


All Replies
Solution
‎03-23-2012 03:04 AM
Super User
Posts: 10,028

Re: Bouble qotes for numeric column in csv

You need a sepcial operator '~'  with option dsd.

data x;
 input a b : ddmmyy10. c d$; 
 format b ddmmyy10.;
cards;
63586900 15/03/2012 4568012145 Kumar 
;
run;
data _null_;
file 'c:\x.csv' dlm=',' dsd;
set x;
put a ~ b c ~ d ~  ;
run;


Ksharp

Message was edited by: xia keshan

Frequent Contributor
Posts: 87

Bouble qotes for numeric column in csv

Thanks Kshar,

I got all values a last help

i am generating almost 47 columns i am getting all values correctly with quotes and comma seperated but

i am not getting output in single line can u help out

i am using

TERMSTR=CRLF  even i am not getting output in single line

SAS Super FREQ
Posts: 8,866

Re: Bouble qotes for numeric column in csv

Hi:

  A different approach would be to use ODS CSV. That destination invocation allows a sub-option called QUOTE_BY_TYPE -- so that your date would NOT be quoted and if your two values were defined as character, they would be quoted.

  In my sample program, NUM1 and NUM2 are the original number variables. I make character versions of those 2 variables (CHAR1 and CHAR2) for the ODS CSV step.

  The output from my program looks like this (without writing any DATA step code):

"char1","date","char2","name"

"63586900",15/03/2012,"4568012145","Kumar"

"12345678",15/11/2012,"9876543212","Kermit"

"32545435",15/04/2012,"6540845521","Gonzo"

"87323621",15/08/2012,"8943245698","Big Bird"

                                                                 

cynthia

** make some data;

data fakedata;
  length char1 $8 date 8 char2 $10 name $8;
  infile datalines dlm=',' dsd;
  input num1 date : ddmmyy10.num2 name $;
  ** convert num1 and num2 to character for next step;
  char1 = put(num1,8.);
  char2 = put(num2,10.);
return;
datalines;
63586900,15/03/2012,4568012145,"Kumar"
12345678,15/11/2012,9876543212,"Kermit"
32545435,15/04/2012,6540845521,"Gonzo"
87323621,15/08/2012,8943245698,"Big Bird"
;
run;
            
** use char1 and char2 to make csv file;
** using the quote_by_type suboption;
ods csv file='c:\temp\makedata.csv'
        options(doc='Help' quote_by_type='yes');
  
proc print data=fakedata noobs;
  var char1 date char2 name;
  format char1 $8. char2 $10. name $8. date ddmmyy10.;
run;
ods csv close;

Frequent Contributor
Posts: 87

Bouble qotes for numeric column in csv

Posted in reply to Cynthia_sas

Hi Cynthia,

i am getting output

"char1","date","char2","name"

"63586900","15/03/2012","4568012145","Kumar"

"12345678","15/11/2012","9876543212","Kermit"

"32545435","15/04/2012","6540845521","Gonzo"

"87323621","15/08/2012","8943245698","Big Bird"

Date column is taking double quotes

SAS Super FREQ
Posts: 8,866

Re: Bouble qotes for numeric column in csv

Hi:

When I run the program in SAS 9.3 and use the QUOTE_BY_TYPE suboption with ODS CSV, my DATE column is NOT quoted. See the attached screenshot.

cynthia


make_csv.jpg
Frequent Contributor
Posts: 87

Bouble qotes for numeric column in csv

Posted in reply to Cynthia_sas

Hi thanks for support

I am getting date values correctly if there is any blank character

it is not taking double quotes how to get double quotes for blank character

Super User
Posts: 10,028

Re: Bouble qotes for numeric column in csv

i am not getting output in single line

Did you mean putting all the rows into only one row ?

data x;
 input a b : ddmmyy10. c d$; 
 format b ddmmyy10.;
cards;
63586900 15/03/2012 4568012145 Kumar 
63586900 15/03/2012 4568012145 Kumar 
63586900 15/03/2012 4568012145 Kumar 
63586900 15/03/2012 4568012145 Kumar 
;
run;
data _null_;
file 'c:\x.csv' dlm=',' dsd recfm=n;
set x;
put a ~ b c ~ d ~  ;
run;


Ksharp

Frequent Contributor
Posts: 87

Bouble qotes for numeric column in csv

Hi Ksharp

not putting all rows in one column i am getting out put of 1st row in 2 lines

after 20th column the row is going to next line

Super User
Posts: 10,028

Bouble qotes for numeric column in csv

OH. you need a newline operator ' / ' .

Suppose need the second line after third variable.

data x;
 input a b : ddmmyy10. c d$; 
 format b ddmmyy10.;
cards;
63586900 15/03/2012 4568012145 Kumar 
;
run;
data _null_;
file 'c:\x.csv' dlm=',' dsd;
set x;
put a ~ b / c ~ d ~  ;
run;

Ksharp

Frequent Contributor
Posts: 87

Bouble qotes for numeric column in csv

Hi,

i am trying to replace the empty date columns with two slash ie.,  /  /  but i am not getting it

how to replace empty dates with  / /

Super User
Posts: 10,028

Re: Bouble qotes for numeric column in csv

OK. How about:

proc format;
value dt(default=10)
 .='//'
 other=[ddmmyy10.] ;
run;
data x;
 input a b : ddmmyy10. c d$ e$; 
 format b dt. ;
cards;
63586900 15/03/2012 4568012145 .  qwqw
63586900 . 4568012145 Kumar wfwsd
;
run;
filename x temp;
data _null_;
file x dlm=',' dsd;
set x;
put a ~ b / c ~ d ~   e ;
run;
data _null_;
infile x;
file 'c:\x.csv';
input;
_infile_=tranwrd(_infile_,'" "','""');
put _infile_;
run;

Ksharp

Message was edited by: xia keshan

Frequent Contributor
Posts: 87

Bouble qotes for numeric column in csv

hi,

yes i am getting date format / / but the characte with blank taking default single space how can i remove single space in put statement " "  i want character as ""

Super User
Posts: 10,028

Re: Bouble qotes for numeric column in csv

OK.

proc format;
value dt(default=10)
 .='//'
 other=[ddmmyy10.] ;
run;
data x;
 input a b : ddmmyy10. c d$ e$; 
 format b dt. ;
cards;
63586900 15/03/2012 4568012145 .  qwqw
63586900 . 4568012145 Kumar wfwsd
;
run;
filename x temp;
data _null_;
file x dlm=',' dsd;
set x;
put a ~ b / c ~ d ~   e ;
run;
data _null_;
infile x;
file 'c:\x.csv';
input;
_infile_=tranwrd(_infile_,'" "','""');
put _infile_;
run;

Ksharp

Message was edited by: xia keshan

Frequent Contributor
Posts: 87

Bouble qotes for numeric column in csv

Thanks Ksharp

Its working

🔒 This topic is solved and locked.

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

Discussion stats
  • 16 replies
  • 746 views
  • 9 likes
  • 4 in conversation