DATA Step, Macro, Functions and more

How to use fileexist function

Accepted Solution Solved
Reply
Contributor
Posts: 44
Accepted Solution

How to use fileexist function

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.


Accepted Solutions
Solution
‎08-31-2011 12:08 PM
Trusted Advisor
Posts: 1,301

How to use fileexist function

Posted in reply to WesBarris

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

View solution in original post


All Replies
Solution
‎08-31-2011 12:08 PM
Trusted Advisor
Posts: 1,301

How to use fileexist function

Posted in reply to WesBarris

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

PROC Star
Posts: 7,492

How to use fileexist function

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

Valued Guide
Posts: 2,177

How to use fileexist function

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

PROC Star
Posts: 7,492

How to use fileexist function

Peter,

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

Valued Guide
Posts: 2,177

How to use fileexist function

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"

Trusted Advisor
Posts: 1,301

How to use fileexist function

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.

Valued Guide
Posts: 2,177

How to use fileexist function

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 quitSmiley Wink

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)

Trusted Advisor
Posts: 1,301

How to use fileexist function

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?

Valued Guide
Posts: 2,177

How to use fileexist function

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)

Contributor
Posts: 44

How to use fileexist function

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!

Contributor
Posts: 44

How to use fileexist function

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

Super User
Super User
Posts: 7,076

How to use fileexist function

Posted in reply to WesBarris

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;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 374 views
  • 4 likes
  • 5 in conversation