topic How to use fileexist function in SAS Programming

How to use fileexist function

WesBarris — Wed, 31 Aug 2011 15:54:43 GMT

I would like to test for the existance of a file. If the file exists I would like to import its contents. If the file does not exist I would like to create a dataset with some variables. I'm not sure how to code this. Here is what I have tried:

data null;

if fileexist("s:\L57MG176\L5776D3.XLS") then

call execute('proc import out=work.ulsound1 datafile="s:\L57MG176\L5776D3.XLS" dbms=excel5 replace; sheet="L5776D3"; getnames=yes;');

else

call execute('data work.ulsound1; input cid aaa; datalines;0 0;');

run;

proc print data=work.ulsound1;

run;

This appears to work if the file is found but I don't think that the datalines part is working when the file is not found.

How to use fileexist function

FriedEgg — Wed, 31 Aug 2011 16:08:22 GMT

The problem is with your syntax in the data step you are calling from the file not exist event. The datalines statement will not work properly without the proper spacing. You should be producing a emtpy dataset rather than a dataset containing a single row with your datalines info.

call execute('data ulsound1; cid=0; aaa=0;');

How to use fileexist function

art297 — Wed, 31 Aug 2011 16:12:23 GMT

I agree with FriedEgg but think that it will run (better?) if it includes a run statement. i.e.,

call execute('data ulsound1; cid=0; aaa=0;run;');

How to use fileexist function

Peter_C — Wed, 31 Aug 2011 20:03:39 GMT

not a run statement I think, but if there is some need to make these datalines take effect, then you need three call execute() statements to separate the datalines from the semi-colons before and after

data;

call execute( 'data; input a b c; cards;');

call execute( ' 1 2 3 ') ;

call execute( ';' );

run;

works

but attaching the ' 1 2 3 ' to the semi-colons either before or after fails

The working solution produced this log

25 data null;

26 if fileexist("s:\L57MG176\L5776D3.XLS") then

27 call execute('proc import out=work.ulsound1 datafile="s:\L57MG176\L5776D3.XLS" dbms=excel5 replace;

27 ! sheet="L5776D3"; getnames=yes;');

28 else do ;

29 call execute('data work.ulsound1; input cid aaa; datalines;');

30 call execute('0 0');

31 call execute(';');

2 The SAS System

32 end ;

33 run;

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

NOTE: DATA statement used (Total process time):

real time 0.02 seconds

cpu time 0.01 seconds

NOTE: CALL EXECUTE generated line.

1 + data work.ulsound1; input cid aaa; datalines;

NOTE: The data set WORK.ULSOUND1 has 1 observations and 2 variables.

NOTE: DATA statement used (Total process time):

real time 0.01 seconds

cpu time 0.01 seconds

3 + ;

35 proc print data=work.ulsound1;

36 run;

NOTE: There were 1 observations read from the data set WORK.ULSOUND1.

NOTE: PROCEDURE PRINT used (Total process time):

real time 0.12 seconds

cpu time 0.00 seconds

How to use fileexist function

art297 — Wed, 31 Aug 2011 20:10:19 GMT

Peter,

It ran for me with just the one statement as long as it included a run statement.

How to use fileexist function

Peter_C — Wed, 31 Aug 2011 20:19:00 GMT

my testing with run; on the datalines version, produced no observation.

guess I'm just demonstrating the message from "FriedEgg"

"datalines statement will not work properly without the proper spacing"

How to use fileexist function

Tom — Wed, 31 Aug 2011 20:20:54 GMT

If you just want one observation you could dispense with the CARDS. You could use a RETAIN statement. Or if you want to initialize everything to the same value you could use an ARRAY statement and take advantage of the NN*value syntax of the initial values.

data ulsound1;

array _num cid aaa (2*0);

run;

How to use fileexist function

FriedEgg — Wed, 31 Aug 2011 20:23:58 GMT

Yes, Peter example of using the multiple execute statements provides the proper output for the datalines statement so it is another way of accomplishing the task. The inclusion of the 'run' statment, while providing a properly closed block but should not effect the resulting output.

How to use fileexist function

WesBarris — Wed, 31 Aug 2011 20:37:40 GMT

Yes. I can confirm that it ran for me too as Art described.

How to use fileexist function

WesBarris — Wed, 31 Aug 2011 20:40:27 GMT

Peter,

The suggested code from FriedEgg did not used the datalines statement. After switching to FriedEgg's code, the example ran properly for me. So, both FriedEgg and Peter have produced working examples for me. One uses the datalines statement and one does not. Thank you both for providing me with a solution!

How to use fileexist function

Peter_C — Wed, 31 Aug 2011 20:45:29 GMT

thank you FriedEgg

I too wish SAS steps had memorable and consistent block/step termination, but like a natural language, SAS has a lot of varieties and exceptions for the end of a step, to the point that I prefer to use what is appropriate. For example RUN does something special and does not end PROC IML (which needs quit;)

There is also something peculiar about the PROC FORMAT which sometimes (during error handling) rejects run; and seeks quit;

I don't recommend run; after datalines.

(but that's just me)

How to use fileexist function

FriedEgg — Wed, 31 Aug 2011 21:04:00 GMT

I have never experienced a behavior like you described with PROC FORMAT, do you have any links to provide me with some refernece to read on the issue?

How to use fileexist function

Peter_C — Wed, 31 Aug 2011 21:46:53 GMT

FriedEgg wrote:

I have never experienced a behavior like you described with PROC FORMAT, do you have any links to provide me with some refernece to read on the issue?

nope

sorry FriedEgg, I have no link nor current evidence.

But some releases-ago, an error popped up from a mixture of cntlin, cntlout and select statements that were unable to find the target member. I ran it several times to be sure. It may be a defect that has been removed since then, but I am cautious with that PROC (while it is still one of my favourites)