topic Re: Macro for calling CSV in SAS Programming

Macro for calling CSV

Kiteulf — Wed, 25 May 2016 12:41:29 GMT

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.

Re: Macro for calling CSV

Reeza — Wed, 25 May 2016 12:44:36 GMT

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.

Re: Macro for calling CSV

RW9 — Wed, 25 May 2016 12:46:03 GMT

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;

Re: Macro for calling CSV

RW9 — Wed, 25 May 2016 12:47:47 GMT

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

Re: Macro for calling CSV

Kiteulf — Wed, 25 May 2016 12:49:30 GMT

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.

Re: Macro for calling CSV

Reeza — Wed, 25 May 2016 12:52:46 GMT

Did you include the keyword replace?

You can also explicitly delete old files using fdelete first.

Re: Macro for calling CSV

RW9 — Wed, 25 May 2016 12:55:07 GMT

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.

Re: Macro for calling CSV

Kiteulf — Wed, 25 May 2016 12:58:41 GMT

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

Re: Macro for calling CSV

Tom — Wed, 25 May 2016 13:11:55 GMT

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 ;

Re: Macro for calling CSV

Loko — Wed, 25 May 2016 13:09:13 GMT

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

Re: Macro for calling CSV

Tom — Wed, 25 May 2016 13:12:17 GMT

Thanks for catching that. I fixed the post.