BookmarkSubscribeRSS Feed
alepage
Barite | Level 11

Hello,

 

I would like to create a macro that check if the excel file is empty or not before doing a proc import.

I am using Sas Enterprise Guide 5.1

 

The idea is the following.  Suppose that I first test if my excel file is empty.

There is only two possible answers : yes or no.

 

If the file is empty do Steps A

Otherwise do Steps B.

 

I am able to ckeck if the file exist first.  But how can I check if the Excel file (*.xlsx) is empty before doing a proc import?

 

%macro test(Dir=, FileName=);

%let filrf=myfile;

%if %sysfunc(fileexist(&Dir.\&FileName.)) %then

%do;

%put the file &FileName exist;

 

%end;

%else %put the file &FileName does not exist;

%mend test;

%test(Dir=c:\Travail\Temp2\, FileName= File1.xlsx);

 

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Define empty?  An xlsx file is never empty, and XLSX file is a ZIP file containing some folders, and some XML files at minimum.  I assume you mean that it contains no data yes?  If so just run the proc import, then test the dataset which is created - either as exist() function, or taking number of observations from the created dataset (can't remember if proc import creates and empty dataset for no data or not).  There's no direct way to ascertain numbe of observations before import, even libname to an xlsx file will do a sort of import.

Criptic
Lapis Lazuli | Level 10
This is the correct way to go about it!
Ksharp
Super User
Check ANY attribute.



data x;
 set sashelp.class;
 stop;
run;

%let dsid=%sysfunc(open(x));
%let any=%sysfunc(attrn(&dsid,any));
%let dsid=%sysfunc(close(&dsid));

%put &any ;

alepage
Barite | Level 11

Hello KSsharp,

 

I have try your solution and it works fine if your worksheet have at least the variable names in it.  I am not sure that it will works with a new excel file (empty).

 

Thanks for your help which is always apprecitated.

Alain

Ksharp
Super User
The code will return different value according to you have no obs, no variable, both no obs and no variable.


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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 5 replies
  • 2364 views
  • 1 like
  • 4 in conversation