How to use fileexist function

[WesBarris]

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.

[FriedEgg · Accepted solution]

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;');

[art297]

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;');

[Peter_C]

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

[art297]

Peter,

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

[Peter_C]

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"

[FriedEgg]

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.

[Peter_C]

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)

[FriedEgg]

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?

[Peter_C]

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)

[WesBarris]

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!

[WesBarris]

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

[Tom]

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;