topic Re: Copying Excel & CSV files with more than 66K rows in SAS Enterprise Guide

Copying Excel & CSV files with more than 66K rows

WilBeach — Wed, 22 Jul 2015 20:16:31 GMT

Hi,

I have a CSV file with 250K rows. How can I copy this file from one folder to another. This is the code I am using:

OPTION NOXWAIT;

DATA RC;

RC = SYSTEM ('copy "G:\Data Files\LAST_MODIFY_DATE_21JUL15_test.CSV" "E:\sasuserfolders\WBEACH\*.*"');

RUN;

I get a Return Code of 0 but the copy did not work because the CSV file has more than 66K rows. When I test the same code on a file with less than 65K row it works.

Thanks,

Wil Beach

Re: Copying Excel & CSV files with more than 66K rows

ballardw — Wed, 22 Jul 2015 20:41:13 GMT

The system copy command should not be file size dependent. In you test with the smaller set did you use a target file name of *.*? I would expect issues with that.

Was the result no file at all, a file but was empty or a file with fewer rows of data than the original? Or something else as "did not work" isn't a very complete description.

Also, were you reusing a target file name?

by any chance have you reversed the source and destination names? Windows Copy wants the source first then the destination.

Re: Copying Excel & CSV files with more than 66K rows

WilBeach — Wed, 22 Jul 2015 20:46:11 GMT

The code works for a smaller file. When using a large file I get a return code of 0 but no file was copied.

Re: Copying Excel & CSV files with more than 66K rows

ballardw — Wed, 22 Jul 2015 20:50:55 GMT

1) Did you reuse a destination name? The default behavior for Copy is to prompt for replace. Since you're not going to get that prompt then that could be the problem. add the /Y switch to test if that may be the case.

Re: Copying Excel & CSV files with more than 66K rows

WilBeach — Wed, 22 Jul 2015 20:56:46 GMT

How should I revise the code below:

OPTION NOXWAIT;

DATA RC;

RC = SYSTEM ('copy "G:\Data Files\LAST_MODIFY_DATE_21JUL15_test.CSV" "E:\sasuserfolders\WBEACH\*.*"');

RUN;

Re: Copying Excel & CSV files with more than 66K rows

Tom — Wed, 22 Jul 2015 21:32:35 GMT

The statement syntax is looks wrong, but apparently Windows supports it. Personally I would remove the *.* from the destination if I wanted to copy the file using the same name it had in the source directory or specify a full name if I wanted to copy it another name.

You can use a PIPE instead of SYSTEM or X or %SYSEXEC to see the error messages from the command.

%let fname=c:\downloads\class.xls;

%let out=c:\downloads\nosuchdir\;

DATA RC;

infile "copy ""&fname"" ""&out"" " pipe;

input ;

put _infile_;

RUN;

0 file(s) copied.

Stderr output:

The system cannot find the path specified.

Re: Copying Excel & CSV files with more than 66K rows

WilBeach — Thu, 23 Jul 2015 13:27:54 GMT

I am running this on a SAS Server and I get an "Access is denied" message (see below). Is there something I can set on the SAS Server to resolve this.

0 file(s) copied.

Stderr output:

Access is denied.

NOTE: 1 record was read from the infile "copy ""G:\Data Files\LAST_MODIFY_DATE_23JUL15.CSV""

""\\rgsss01\marketing$\Monica\James Emails\"" ".

The minimum record length was 25.

The maximum record length was 25.

NOTE: The data set WORK.RC has 1 observations and 0 variables.

Re: Copying Excel & CSV files with more than 66K rows

Tom — Thu, 23 Jul 2015 13:35:14 GMT

It probably means that you do not have permission to write to that folder. It might mean that the file is already there and someone has it open so Windows is not letting you overwrite it.

Re: Copying Excel & CSV files with more than 66K rows

WilBeach — Thu, 23 Jul 2015 13:52:57 GMT

When I run this same code in Enterprise Guide it works. When I run on SAS Server I get "Access Denied". Is there something on the SAS Server I can set to resolve this.

Thanks.

Re: Copying Excel & CSV files with more than 66K rows

Ksharp — Thu, 23 Jul 2015 14:09:36 GMT

If you have SAS9.4 , also check function FCOPY() .

Re: Copying Excel & CSV files with more than 66K rows

WilBeach — Thu, 23 Jul 2015 14:14:11 GMT

Would FCOPY() resolve the "Access Denied" issue?

Re: Copying Excel & CSV files with more than 66K rows

Ksharp — Thu, 23 Jul 2015 14:16:32 GMT

NO. That OS Admin 's thing .

Re: Copying Excel & CSV files with more than 66K rows

WilBeach — Thu, 23 Jul 2015 14:20:56 GMT

I am set up as an Administrator by I don't know what to change. We are a small company where the IT Tech did the install and we are on our own after that.

Re: Copying Excel & CSV files with more than 66K rows

ArthurWilliams — Thu, 23 Jul 2015 14:25:41 GMT

If your SAS Enterprise Guide session is connected to the local host (PC SAS) you would have permissions to use system command. With server installations of SAS system commands are usually disabled for the users.

You may want to look at this blog as an alternative <http://blogs.sas.com/content/sasdummy/2013/09/17/copy-file-macro/>

Re: Copying Excel & CSV files with more than 66K rows

ArthurWilliams — Thu, 23 Jul 2015 14:46:50 GMT

Look in the SASV9*.cfg files for -noxwait and -noxcmd or sas*.bat files (in Windows) and sas*.sh (on Linux or Unix). if the -noxcmd is changed to -xcmd then the system will allow system command.

Re: Copying Excel & CSV files with more than 66K rows

Reeza — Thu, 23 Jul 2015 14:57:46 GMT

I think your server can't see your E drive. Try a different drive.

You'll need to set up your server to either see your E drive or write it to a different location.

Re: Copying Excel & CSV files with more than 66K rows

WilBeach — Thu, 23 Jul 2015 15:20:17 GMT

Any SAS Server Admin out there can help me set up the SAS Server to access any folder on our server?

Thanks, Wil.

Re: Copying Excel & CSV files with more than 66K rows

ArthurWilliams — Thu, 23 Jul 2015 16:07:02 GMT

Try replacing the drive reference "E:sasuserfolders\wbeach\*.*" with the UNC path \\machinename\sasuserfolders\WBEACH\*.* where "machinename' is the name of the computer that the E: drive is associated.

on the Windows server launch the "Computer Manager" and look for the "Storage" section on the left, expand "Storage" and click on "Disk Management" this will list all of the disks on the server. If the E: drive is not listed then it is not attached to the server and you will need to use the UNC or Map the drive (if you map the drive it will only be available to your ID/profile and is not a global map.).

Re: Copying Excel & CSV files with more than 66K rows

WilBeach — Thu, 23 Jul 2015 17:04:44 GMT

Arthur, thanks for all your help. Here is my production code:

%let fname=G:\Data Files\LAST_MODIFY_DATE_&SYSDATE..CSV;

%let out=\\rgsss01\marketing$\Monica\James Emails\;

DATA RC;

infile "copy ""&fname"" ""&out""" pipe;

input ;

put _infile_;

RUN;

I get an "Access Denied" on \\rgsss01\marketing$\Monica\James Emails\

The attached file displays what I have access to using Explorer & Disk Management. To me it looks like I have access to everything.

Thanks again,

Wil

Re: Copying Excel & CSV files with more than 66K rows

ArthurWilliams — Thu, 23 Jul 2015 17:25:18 GMT

Wil, it looks like you do not have permissions to the directory, are you able to navigate using Windows Explorer to \\rgsss01\marketing$\Monica\James Emails\ if you are not able to then you will need to be granted permissions to the directory.

FYI there is no attachment, try inserting an image.