topic Bouble qotes for numeric column in csv in SAS Procedures

Bouble qotes for numeric column in csv

santhosh — Fri, 23 Mar 2012 05:32:33 GMT

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"

Re: Bouble qotes for numeric column in csv

Ksharp — Fri, 23 Mar 2012 07:04:04 GMT

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

Bouble qotes for numeric column in csv

santhosh — Fri, 23 Mar 2012 11:47:00 GMT

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

Re: Bouble qotes for numeric column in csv

Cynthia_sas — Fri, 23 Mar 2012 14:21:40 GMT

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;

Re: Bouble qotes for numeric column in csv

Ksharp — Sat, 24 Mar 2012 12:55:53 GMT

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

Bouble qotes for numeric column in csv

santhosh — Mon, 26 Mar 2012 07:16:52 GMT

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

Bouble qotes for numeric column in csv

santhosh — Mon, 26 Mar 2012 08:56:38 GMT

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

Bouble qotes for numeric column in csv

Ksharp — Mon, 26 Mar 2012 09:09:38 GMT

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

Bouble qotes for numeric column in csv

Tom — Mon, 26 Mar 2012 11:50:17 GMT

Sounds like you did not set a long enough line length on your FILE statement. You should see notes in the log about how many lines written and the maximum line size written. Use the LRECL option.

Re: Bouble qotes for numeric column in csv

Cynthia_sas — Mon, 26 Mar 2012 14:28:21 GMT

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

Bouble qotes for numeric column in csv

santhosh — Wed, 28 Mar 2012 07:45:02 GMT

Thanks Tom

its working fine

Bouble qotes for numeric column in csv

santhosh — Wed, 28 Mar 2012 11:52:03 GMT

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 / /

Re: Bouble qotes for numeric column in csv

Ksharp — Thu, 29 Mar 2012 02:19:43 GMT

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

Bouble qotes for numeric column in csv

santhosh — Thu, 29 Mar 2012 06:01:18 GMT

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

Bouble qotes for numeric column in csv

santhosh — Thu, 29 Mar 2012 07:09:38 GMT

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

Re: Bouble qotes for numeric column in csv

Ksharp — Thu, 29 Mar 2012 07:11:34 GMT

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

Bouble qotes for numeric column in csv

santhosh — Thu, 29 Mar 2012 11:36:00 GMT

Thanks Ksharp

Its working