BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
WesBarris
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
FriedEgg
SAS Employee

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

12 REPLIES 12
FriedEgg
SAS Employee

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
Opal | Level 21

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
Rhodochrosite | Level 12

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
Opal | Level 21

Peter,

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

Peter_C
Rhodochrosite | Level 12

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
SAS Employee

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
Rhodochrosite | Level 12

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
SAS Employee

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
Rhodochrosite | Level 12

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
Obsidian | Level 7

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
Obsidian | Level 7

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

Tom
Super User Tom
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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