export to CSV using macro

[kfluegge]

I would like to export to csv file using the SAS utility macro %DS2CSV Macro. i am not able to download this macro. Where may I find it? I am using Enterprise Guide.

[Reeza]

It's usually installed by default. I just tried and I can call it, and I haven't changed my base installation since it was updated a few weeks ago.

[PeterClemmensen]

It should be installed by default?

[Tom]

I see it on Unix at

.../sas9.4/SASFoundation/9.4/sasautos/ds2csv.sas

And on Windows (PC) at

...\SASHome\SASFoundation\9.4\core\sasmacro\ds2csv.sas

I think this macro was original generated for SAS/Intrnet so you might need to have that licensed to have it.

Note that it is trivial to write a CSV file without the macro.  There are many posts on that topic in these forums.

[Reeza]

/*Method 1*/
proc export data=sashelp.retail outfile='/folders/myfolders/demo1.csv' dbms=csv replace;run;


/*Method 2*/
ods csvall file='/folders/myfolders/demo2.csv';

proc print data=sashelp.retail noobs ;
run;

ods csvall;

/*Method 3*/
%ds2csv (data=sashelp.retail, runmode=b,  csvfile=/folders/myfolders/retail.csv);

All three of these work out of the box for me. 

[kfluegge]

When I use method 1, I receive the message saying "ERROR: Physical file does not exist."

[Reeza]

What version of SAS are you using that you don't have SASHELP installed? Check your version with:

proc product_status;run;


You can replace my paths and data set names with your own paths and datasets then to run the demo, sashelp.class should be safe.

If you're using WPS then obviously this won't work.

[kfluegge]

My product version is below:

For Base SAS Software ...
Custom version information: 9.4_M4
Image version information: 9.04.01M4P110916
For SAS/STAT ...
Custom version information: 14.2
For SAS/GRAPH ...
Custom version information: 9.4_M4
For SAS Integration Technologies ...
Custom version information: 9.4_M4
For High Performance Suite ...
Custom version information: 2.2_M5
For SAS/ACCESS Interface to PC Files ...
Custom version information: 9.4_M4
For SAS/ACCESS Interface to ODBC ...
Custom version information: 9.4_M4

I replace your paths with my own and receive the message "ERROR: Physical file does not exist, c:\folders\myfolders\demo1.csv."

[Reeza]

I suspect that path doesn't exist on your computer unless you created it. Please post the exact log and code you used.

If you're using SAS EG, you may only be able to write to the server as well, if your system is locked down.

[kfluegge]

The full code is below. 

%LET CONDITION=HF; 											
%LET YEAR=1216;												
%LET PATH1= \\naslocshare240\agencyshare\SPARCS Hospital Readmissions;      				
LIBNAME RAW "&PATH1"; 											

libname sp0717 odbc noprompt="SERVER=SQLIT06C\IT06C;DRIVER=SQL Server Native Client 11.0;
Trusted_Connection=YES;DATABASE=SPARCS_MHYEPI_201707" schema='dua1309iden'; 

libname sp0118 odbc noprompt="SERVER=SQLIT06C\IT06C;DRIVER=SQL Server Native Client 11.0;
Trusted_Connection=YES;DATABASE=SPARCS_MHYEPI_201801" schema='dua1309iden';

proc format;
value raceethf    1='Hispanic'
		  2='NH White'
		  3='NH Black'
		  4='NH Asian/Pacific Islander'
		  5='NH Other';
run;

*Read in data from 2012-2016;
data IP_&YEAR;
set 
sp0717.IPSPC12_0717
sp0118.IPSPC13_0118 
sp0118.IPSPC14_0118
sp0118.IPSPC15_0118
sp0118.IPSPC16_0118;

run;

proc export data=IP_&YEAR outfile='R:\SPARCS Hospital Readmissions\AAMC Deliverables\all\inpatient.csv' dbms=csv replace;run;

The log is below:

ERROR: Physical file does not exist, R:\SPARCS Hospital Readmissions\AAMC Deliverables\all\inpatient.csv.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: There were 1 observations read from the data set WORK.IP_1216.
NOTE: DATA statement used (Total process time):
      real time           0.04 seconds
      cpu time            0.04 seconds
      

0 records created in R:\SPARCS Hospital Readmissions\AAMC Deliverables\all\inpatient.csv from IP_1216.
  
  
NOTE: "R:\SPARCS Hospital Readmissions\AAMC Deliverables\all\inpatient.csv" file was successfully created.
NOTE: PROCEDURE EXPORT used (Total process time):
      real time           0.14 seconds
      cpu time            0.15 seconds

[Reeza]

Yeah, you're running on a server so it can't write to a network drive or your hard drive. You'll have to export it to a server and then download the file or have a shared location set up, that both your server and desktop can access.

[kfluegge]

How do I export to a server? 

[Tom]

Why would you use an UNC for the LIBNAME statement but try to use a drive letter for that filename?

Your SAS server probably does not have that drive mapped to that letter.

Are the both supposed to pointing to the same place?

%LET PATH1= \\naslocshare240\agencyshare\SPARCS Hospital Readmissions;      				
LIBNAME RAW "&PATH1"; 	
...
outfile="&path1\AAMC Deliverables\all\inpatient.csv"
...

[kfluegge]

yes, they are pointing to the same place.

[Tom · Accepted solution]

---

@kfluegge wrote:

yes, they are pointing to the same place.

---

So if the LIBNAME works then try using the same path as part of the filename you are trying to create instead of trying to use a drive letter that might not exist on the machine where SAS is running.

Also check that you have WRITE access to that location.