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"
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
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
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
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;
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
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
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
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
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
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
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 / /
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
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 ""
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
Thanks Ksharp
Its working
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.