topic Re: SAS Fileexist Query in SAS Programming

SAS Fileexist Query

scb — Wed, 08 Aug 2018 09:28:52 GMT

I run the below codes but could not import the external file despite meeting the criteria. Anyone can help? Thanks.

data test;
input brand $;
cards;
BMW
;
run;

proc sql noprint;
select distinct brand
into :brand separated by ''
from test;
quit;

%let dsid=%sysfunc(open(test));
%let num=%sysfunc(attrn(&dsid,nobs));
%let rc=%sysfunc(close(&dsid));

%put &dsid;
%put &num;
%put &rc;

/**if number of row = 1 and excel file exist**/
%macro checkds;
%if &num eq 1 or %sysfunc(fileexist(&brand_.xlsx)) %then %do;
PROC IMPORT OUT=brand
DATAFILE="D:\&brand..xlsx"
DBMS=EXCEL REPLACE;
GETNAMES=YES;
run;
%end;
%else %do;
stop;
%end;
%mend checkds;

Re: SAS Fileexist Query

Tom — Wed, 08 Aug 2018 09:38:34 GMT

Your call to FILEEXIST() is looking for a different file than you are using in your PROC IMPORT code.

%if &num eq 1 or %sysfunc(fileexist(&brand_.xlsx)) %then %do;
PROC IMPORT OUT=brand DATAFILE="D:\&brand..xlsx"
...

The first one is using a macro variable name BRAND_ and the second is using a macro variable named BRAND.

The top part of your program is a little confusing also.

Are you trying to concatenate all of the values of BRAND into a single macro variable?

Or do you expect your input dataset to have only one value of BRAND?

Or are you trying to loop over the lists of brands in the dataset and import multiple XLSX files?

Re: SAS Fileexist Query

RW9 — Wed, 08 Aug 2018 10:16:02 GMT

So, your using a guessing procedure to guess what the data should like as you don't know. You are using the worst possible data format file which has no structural rigidity. And then to compound it you don't even know if the file exists?

And this process is going to work? (clue, answer is no it will not work).