Hello
i have data which is combination of character, numeric and date value. i want to export this dataset in csv format.
i want to enclose all character values in double quotes, numeric and date values just seperated by comma. data will also have null values for all character/numeric/date values. First line of file will have title/heading
Example data has columns
like Name-Character, DOB date format mmddyy10. and Age is number format.
Desired output file
title/heading of file
"Name" , "DOB" , "Age"
"john, kalan" , 1/1/2017 , 3
"john kalan" , ,
" " , 1/1/2017 , 3
Has anyone of you tried my code?
data test;
infile datalines4 dlm=';' dsd truncover;
input name :$20. dob : mmddyy10. age;
format dob mmddyy10.;
datalines4;
Frank Jones;1/1/2017;3
Frank,Jones;1/1/2017;3
;;;;
run;
data _null_;
file '$HOME/test.txt' dlm=',' dsd;
set test;
if _n_ = 1 then put '"Name","DOB","Age"';
put name ~ dob age;
run;
creates this text file:
"Name","DOB","Age" "Frank Jones",01/01/2017,3 "Frank,Jones",01/01/2017,3
which is the OP's intention, as I guess.
Thanks for your reply, i am using this
proc export data=&datast
outfile="test.csv"
dbms=dlm
label
replace;
delimiter=',' ;
run;
above is my code. but only character values with comma etc are double quoted but one with spaces are not.
desired output
"Name" , "DOB" , "Age"
"john, kalan" , 1/1/2017 , 3
"john kalan" , ,
" " , 1/1/2017 , 3
Use this data step:
data _null_;
file "test.csv" dlm=',' dsd;
set &datast;
if _n_ = 1 then put '"Name","DOB","Age"';
put name ~ dob age;
run;
For such a specific solution, you might find the EXPORT Procedure fails to meet the need.
A DATA Step provides all the flexibility you seek and with brevity.
____________________________
Proc contents noprint out= _data_ data= your.data ;
Proc sort; by varnum ; run ;
FILENAME fref "the path/filename.csv" lrecl= 10000 ;
Data _null_ ;
File fref dsd ;
Set ;
Put name ~ @@ ;
Run ;
* the tilde~modilier in PUT statements would "quote" all variables output, including numeric
values. As this is not wanted an alternate quoting method is used for the output of the data rows ;
Data _null_ ;
File fref mod dsd ;
Set your.data ;
Format _character_ $quote200. ;
Put ( _all_ )( : ) ;
run ;
* need to release the file so other apps can open it;
FILENAME fref ;
___________________________________
That solution is quite generalised but might fail for character variables that are filled wider than 198.
Also as I defined LRECL= 10000, should column names or data values require a line wider than that these beyond this limit will be lost. The NOTEs in the SASlog will indicate the widest line written providing an easy check.
here is my final working code
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Proc contents noprint out= _data_ data= your.data ;
Proc sort; by varnum ; run ;
FILENAME fref "%PATH(YOUR)/filename.csv" lrecl= 10000 ;
Data _null_ ;
File fref dsd ;
Set ;
Put name ~ @@ ;
Run ;
* the tilde~modilier in PUT statements would "quote" all variables output,
including numeric values. As this is not wanted an alternate quoting
method is used for the output of the data rows ;
Data _null_ ;
File fref mod dlm=',' ;
Set your.data ;
Format _character_ $quote200. ;
Put ( _all_ )( ~ ) ;
run ;
* need to release the file so other apps can open it;
FILENAME fref ;
Try ODS CSV . filename temp temp; ods _all_ close; ods csv file=temp options(quote_by_type='yes'); proc print data=sashelp.class noobs;run; ods csv close; data _null_; infile temp; input; put _infile_; run;
Has anyone of you tried my code?
data test;
infile datalines4 dlm=';' dsd truncover;
input name :$20. dob : mmddyy10. age;
format dob mmddyy10.;
datalines4;
Frank Jones;1/1/2017;3
Frank,Jones;1/1/2017;3
;;;;
run;
data _null_;
file '$HOME/test.txt' dlm=',' dsd;
set test;
if _n_ = 1 then put '"Name","DOB","Age"';
put name ~ dob age;
run;
creates this text file:
"Name","DOB","Age" "Frank Jones",01/01/2017,3 "Frank,Jones",01/01/2017,3
which is the OP's intention, as I guess.
Thank you for solution it works as expected.
Only issue i see is
when i do if _n_ = 1 then put statement i have close to 20 columns, so when i export to csv format and if i press enter for names since 20 is lot to read on one line. csv output breaks the line with tab or enter
example
if _n_ = 1 then put '"Col1", "Col2", "Col3", "Col4", "Col5", "Col6", "Col7", "Col8", "Col9", "Col10",
"Col11", "Col12", "Col13", "Col14", "Col15", "Col16", "Col17",
so in csv output there is extra space between Col10 and Col11 and is causing issue in csv file. Is there way to fix it?
example
"Col9","Col10", "Col11","Col12"
Split your put statement:
put '"col1","col2","col3",'@;
put '"col4","col5","col6"';
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 how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.