08-30-2017 10:22 PM
I want to export SAS data to csv file with all fields quoted.
I tried every method listed in following site and numeric fields with missing value are exported to " " (a quoted single space) but I need missing values to be exported as "" (nothing between two quote marks).
As a workaround I use option missing='7f'x to export to a temp file and then export to final result by compressing the '7f'x character.
Is there any smarter method that I can get the same result?
08-30-2017 10:48 PM
Are you using a data step to export? If so, can you post your code?
A brute force method is
if missing(var) then put " ";
else put ...
08-31-2017 01:46 AM
I am using such a code.
options missing=''; data _null_; file "D:\temp\class.csv" dsd dlm=','; set sashelp.class; put (_all_) (~); run; options missing='.';
I an writing the code so that the missing value is "", but it will output with " " (a quoted single byte space).
08-31-2017 02:10 AM
08-31-2017 02:24 AM
Seems like a silly requirement, but if you are willing to use two steps it is not too hard.
Let's make some test data with missing character and numeric values.
data test ; set sashelp.class (obs=3); if _n_=2 then call missing(sex); if _n_=1 then call missing(age); run;
So first wrtie to a temporary file. Then read the temoporary file and "fix" the lines and re-write them. Add a comma to tbe beginning and end of the lines to make it easier. That way you can just convert both ," ", and ,".", into ,"",. You can then drop the extra commas.
filename step1 temp; data _null_; set test; file step1 dsd dlm=',' ; put ',' (_all_) (~) +(-1) ','; run; data _null_; infile step1 ; file 'want.csv' ; input ; _infile_=tranwrd(_infile_,'," ",',',"",'); _infile_=tranwrd(_infile_,',".",',',"",'); _infile_=substr(_infile_,2,length(_infile_)-2); put _infile_; run;
09-04-2017 09:20 PM
Thank you for your response.
As a result I decided to use two steps. First I export to csv file by replacing missing values with characters that I do not use, and then deleted that character.
options missing='7f'x; data _null_; file "D:\temp\baseball.csv" dsd dlm=','; set sashelp.baseball; put (_all_) (~); run; options missing='.'; data _null_; infile "D:\temp\baseball.csv"; file "D:\data\baseball.csv"; input; _infile_=compress(_infile_,'7f'x); put _infile_; run;
09-05-2017 08:06 AM
Because blank space is not accepted as a numeric field in a specification of outside system, I am trying to export csv file without a space between two quote marks.
Does this answer match the intention of the question?