Macro for calling CSV

Accepted Solution Solved
Reply
Contributor
Posts: 40
Accepted Solution

Macro for calling CSV

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.


Accepted Solutions
Solution
‎05-25-2016 08:57 AM
Super User
Super User
Posts: 7,720

Re: Macro for calling CSV

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;

View solution in original post


All Replies
Super User
Posts: 19,157

Re: Macro for calling CSV

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. 

 

 

Super User
Super User
Posts: 7,720

Re: Macro for calling CSV

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

Solution
‎05-25-2016 08:57 AM
Super User
Super User
Posts: 7,720

Re: Macro for calling CSV

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;
Contributor
Posts: 40

Re: Macro for calling CSV

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.

Super User
Posts: 19,157

Re: Macro for calling CSV

Did you include the keyword replace?

 

You can also explicitly delete old files using fdelete first. 

 

Super User
Super User
Posts: 7,720

Re: Macro for calling CSV

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.

Contributor
Posts: 40

Re: Macro for calling CSV

 

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

Super User
Super User
Posts: 6,845

Re: Macro for calling CSV

[ Edited ]

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 ;
Super Contributor
Posts: 308

Re: Macro for calling CSV

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

Super User
Super User
Posts: 6,845

Re: Macro for calling CSV

Thanks for catching that. I fixed the post.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 562 views
  • 1 like
  • 5 in conversation