BookmarkSubscribeRSS Feed
acordes
Rhodochrosite | Level 12

I want to include a ETL code if the file sashdat does not exist. 

But it does not work.

 

What works is the data _null_ on its own.

FILENAME REFFILE FILESRVC FOLDERPATH='/Projects/Residual_Value 3.0/'  FILENAME='_etl_fsvbala.sas';
data _null_;
%include reffile;
run;

But I don't know how to check for the existence of a certain file in sashdat format.

The global logic what I want to achieve goes like this:

If the file sashdat exists and the table with the same name is not loaded into memory, then load and promote the table. 

When neither file sashdat nor table exist, then include the ETL code shown above. 

 

So I need to check for the file existence in the caslib and use this true/false variable to decide on running the %include statement. 

 

I know how to check for the existence of a table. I would like to adapt this code to file existence, but I suppose that I cannot use the data _null_ as a sourcecode block in proc cas. 

 

FILENAME REFFILE FILESRVC FOLDERPATH='/Projects/Residual_Value 3.0/'  FILENAME='_etl_fsvbala.sas';

%let casy=FSVBALA_&fc._OUTLIER;
%let liby=risknoba;

/* works */
cas mysession sessopts=(caslib="&liby");
proc cas;        
    table.tableExists result=e status=rc / 
        caslib = "&liby"
        name   = "&casy"
    ;
    /* If the data exists, set dataExists = 1 and grab the current report data */
    if(e.exists = 0) then do;
     table.loadtable / path="&casy..sashdat" casOut="&casy" caslib="&liby"; 
     table.promote /name="&casy" ;
print "hello";
  end;
run;

/* does not work properly, it executes the code but with error messages */
proc cas;        
    table.tableExists result=e status=rc / /* fileExists???
        caslib = "&liby"
        name   = "&casy";
    if(e.exists ^= 0) then do;

source myDataStep;
data _null_;
%include reffile;
run;
endsource;
   dataStep.runCode result=r status=rc / 
     code=myDataStep;
end;
run;
1 REPLY 1
acordes
Rhodochrosite | Level 12

I've tried to circumvent by using a call symputx within proc cas. 

 

That works half-way because my coditional %include fires whatever value the macro variable file_true has. 🙄

 


%let casy=FSVBALA_&fc._OUTLIER;
%let liby=risknoba;

cas mysession sessopts=(caslib="&liby");
proc cas;        
    table.tableExists result=e status=rc / 
        caslib = "&liby"
        name   = "&casy"
    ;
      call symputx("file_true", e.exists);
    /* If the data exists, set dataExists = 1 and grab the current report data */
    if(e.exists = 0) then do;
     table.loadtable / path="&casy..sashdat" casOut="&casy" caslib="&liby"; 
     table.promote /name="&casy";
  end;
run;

%put &file_true;

options mlogic symbolgen;

FILENAME REFFILE FILESRVC FOLDERPATH='/Projects/Residual_Value 3.0/'  FILENAME='_etl_fsvbala.sas';
data _null_;
if &file_true.=0 then do;
%include reffile;
end;
run;

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

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
  • 1 reply
  • 274 views
  • 0 likes
  • 1 in conversation