BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
santhosh
Fluorite | Level 6

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"

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

16 REPLIES 16
Ksharp
Super User

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

santhosh
Fluorite | Level 6

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

Cynthia_sas
SAS Super FREQ

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;

santhosh
Fluorite | Level 6

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

Cynthia_sas
SAS Super FREQ

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
santhosh
Fluorite | Level 6

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

Ksharp
Super User

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

santhosh
Fluorite | Level 6

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

Ksharp
Super User

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

santhosh
Fluorite | Level 6

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

Ksharp
Super User

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

santhosh
Fluorite | Level 6

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

Ksharp
Super User

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

santhosh
Fluorite | Level 6

Thanks Ksharp

Its working

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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