topic Re: export to CSV using macro in New SAS User

export to CSV using macro

kfluegge — Mon, 14 Jan 2019 19:43:48 GMT

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.

Re: export to CSV using macro

Reeza — Mon, 14 Jan 2019 19:49:32 GMT

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.

Re: export to CSV using macro

PeterClemmensen — Mon, 14 Jan 2019 19:57:38 GMT

It should be installed by default?

Re: export to CSV using macro

Tom — Mon, 14 Jan 2019 20:03:05 GMT

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.

Re: export to CSV using macro

Reeza — Mon, 14 Jan 2019 20:07:20 GMT

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

Re: export to CSV using macro

kfluegge — Mon, 14 Jan 2019 20:36:01 GMT

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

Re: export to CSV using macro

Reeza — Mon, 14 Jan 2019 20:39:22 GMT

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.

Re: export to CSV using macro

kfluegge — Mon, 14 Jan 2019 20:46:57 GMT

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

Re: export to CSV using macro

Reeza — Mon, 14 Jan 2019 20:48:18 GMT

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.

Re: export to CSV using macro

kfluegge — Mon, 14 Jan 2019 20:53:19 GMT

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

Re: export to CSV using macro

Reeza — Mon, 14 Jan 2019 21:43:34 GMT

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.

Re: export to CSV using macro

Tom — Mon, 14 Jan 2019 21:55:45 GMT

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

Re: export to CSV using macro

kfluegge — Tue, 15 Jan 2019 13:30:26 GMT

How do I export to a server?

Re: export to CSV using macro

kfluegge — Tue, 15 Jan 2019 13:34:23 GMT

yes, they are pointing to the same place.

Re: export to CSV using macro

Tom — Tue, 15 Jan 2019 16:29:35 GMT

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