Macro for calling CSV

[Kiteulf]

I am having difficulties calling on the following macro

%macro savecsv(dsin=, outfile=, outname=);

libname csv odbc noprompt="DSN=csv;ReadOnly=0;DBQ=&outfile.;";

proc datasets lib=csv nolist;
delete &outname.;
quit;

data csv.&outname.; set &dsin;
run;

libname csv;
%mend;

with

%savecsv(dsin=WORK.DAY2,
outfile=\\File\Reports\DAY2.csv,
outname=DAY2);

I am getting 

ERROR: CLI error trying to establish connection: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver
specified
ERROR: Error in the LIBNAME statement.
ERROR: Libref CSV is not assigned.

[Reeza]

I don't believe there's a libname for CSV. 

If if you want to automate the input then use a proc import inside your macro. 

[RW9]

At a guess, he is trying to ODBC to Excel.  

[RW9 · Accepted solution]

You have a problem with your ODBC driver as called in the libname csv odbc line.  Either the details you provide are not correct, the odbc isn't in the necessary location - be that on network, your machine or somewhere elese where SAS is being run, or it is not configured correctly.  You would need to lay the problem out to your IT group so they can fix it.  

Although, why do you need to use ODBC to create a CSV in the first place?  Proc export can do it quite easily, or a data _null_ step and put the data items out, verfy simple Base SAS syntax, no need for macros and ODBC drivers etc.

proc export data=sashelp.class outfile='c:\file.csv' dbms=csv replace;
run;

[Kiteulf]

Allright but if I use the proc export function, it creates a lot of .bak files when this file is supposed to be replaced.

I can't have that.

[Reeza]

Did you include the keyword replace?

You can also explicitly delete old files using fdelete first. 

[RW9]

What OS are you using?  I assume it is not Windows, as running a proc export will fail if file exists and you don't specify the replace command, so its likely your OS creating those files.

[Kiteulf]

I was doing it with an export to excel first but that failed with the .bak files. exporting with csv did not produces the .bak files...

[Tom]

There is no need to use ODBC or PROC EXPORT to create a CSV file. It is just a delimited file and SAS already knows how to write delimited files.  Only "trick" is getting the header line.

%macro savecsv(dsin=, outfile=);
proc transpose data=&dsin(obs=0);
 var _all_;
run;
data _null_;
   set &syslast end=eof;
   file "&outfile" dsd ;
   put _name_ @;
   if eof then put;
run;
data _null_;
   set &dsin;
   file "&outfile" dsd mod ;
   put (_all_) (+0) ;
run;
%mend savecsv ;

[Loko]

@Tom nice code - just for other readers the end=eof shall be on set statement.

[Tom]

Thanks for catching that. I fixed the post.