BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Kiteulf
Quartz | Level 8

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.

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

10 REPLIES 10
Reeza
Super User

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
Diamond | Level 26 RW9
Diamond | Level 26

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Quartz | Level 8

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
Super User

Did you include the keyword replace?

 

You can also explicitly delete old files using fdelete first. 

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Quartz | Level 8

 

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
Super User Tom
Super User

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
Barite | Level 11

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

Tom
Super User Tom
Super User

Thanks for catching that. I fixed the post.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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