topic Re: Retrieve the list of files in the configured folder then import these different xlsx files in SAS Programming

Retrieve the list of files in the configured folder then import these different xlsx files

Gick — Thu, 05 Oct 2023 08:52:29 GMT

%macro import_files(BIO=);
	%if %length(&BIO.) > 0 %then %do;
		/* 1) */
		filename filelist "&BIO.";
		data filelist;
			length filename $256;
			rc = filename('dir', "&BIO.");
			if rc = 0 then
				did = dopen('dir');
			if did > 0 then do while(dread(did) = 0);
				filename = dread(did);
				output;
			end;
			rc = dclose(did);
		run;

		/* 2)  */
		data _null_;
			set filelist;
			call symputx('filename', filename);
			call symputx('filelist', ifc(length(filelist) > 0, "&filelist. " || filename, filename));
		run;

		%let filelist = %sysfunc(compbl(&filelist.));

		%if %length(&filelist.) > 0 %then %do;
			%let cnt = %sysfunc(countw(&filelist.));

			/* 3) */
			%do i = 1 %to &cnt.;
				%let l = %scan(&filelist., &i.);
				
				libname xlsx "&BIO.";
				proc import datafile="&BIO.\&l." out=WORK.Labo&l.
					dbms=XLSX replace;
					getnames=yes;
				run;
				libname xlsx clear;

				
				data BDD_labo;
					set BDD_labo Labo&l.;
				run;
			%end;
		%end;
	%end;
%mend;

%import_files(BIO="C:/DATA/BIO");

Hi,

/* 1) retrieve the list of files in the folder configured in BIO
2) for each file, without sorting them according to their name, create a list containing the different names and do a do loop to automate the import of all files
3) each imported base is called Labo&l., and at the end of each import, add a data step to insert the results on the same base which will be called BDD_labo */
Can someone correct my code. I don't know why it doesn't work. No results and in the log, I get "NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release. Inserting white space" "WARNING: The quoted string currently being processed has become more than 262 characters long. You might have unbalanced quotation "

Another approach which allows the same steps to be carried out will be welcome.

Thanks in advance.
Gick

Re: Retrieve the list of files in the configured folder then import these different xlsx files

Kurt_Bremser — Thu, 05 Oct 2023 09:20:37 GMT

Macro development needs to start with working non-macro code. So get rid of the macro definition and make your code work step-by-step (Maxim 34).

Your DATA _NULL_ step cannot work. You try to use a macro variable in the code which you create in the step. Since the macro variable reference is resolved before the data step is even compiled, it won't find the variable. Instead of the DATA step, use a PROC SQL SELECT INTO to create the macro variable

proc sql noprint;
select filename into :filelist separated by " "
from filelist;
quit;

Somewhere in your code, you need to make sure that you only read filenames ending in .xlsx, then you have to extract a part of the filename before the .xlsx which constitutes a valid SAS name. Create code which does this successfully for a single file before wrapping it (and the import step) in a %DO loop.

I also recommend to create a new directory for the target library; mixing datasets and non-SAS files in a library is not a good idea.

Your error message indicates that your SAS session has become unstable, either by submitting unbalanced quotes, an incomplete macro definition, or other similar causes. Start a new session before following the above advice.

Finally, when you're ready to create the macro, make every macro variable used in the macro local (%LOCAL) to it, so you never have accidental side-effects when calling the macro.

Re: Retrieve the list of files in the configured folder then import these different xlsx files

s_lassen — Thu, 05 Oct 2023 09:26:01 GMT

Your code contains several errors. Have you tried running step 2 outside the macro? It will not work (the &FILELIST reference only gets initialized once, when the data step is compiled).

Rather than trying to loop inside the macro, I would write a small macro for a single import step (and test that), and then have the first data step write the code to execute for all files.

The macro would be something like this:

%macro bio_imp(l);
proc import datafile="&BIO.\&l." out=WORK.Labo
	dbms=XLSX replace;
	getnames=yes;
run;

proc append base=BDD_labo data=Labo force;
run;
%mend;

(I assume that you were trying to collect all the data into BDD_LABO with the last data step, but what your code does is just to overwrite with the newest read table). I did not put the (physical) filename into the output dataset name, as it is probably not a valid name for a SAS table.

Test the macro with a couple of files, to see that it works as intended.

To execute the imports, write code to execute all the macro calls, something like this:

proc delete data=BDD_labo;run; /* if the final data already exists, delete it */

data filelist;
	length filename $256;
	rc = filename('dir', "&BIO.");
	if rc = 0 then
		did = dopen('dir');
	if did > 0 then do while(dread(did) = 0);
		filename = dread(did);
		output;
		end;
	rc = dclose(did);
run;

filename tempsas temp;
data _NULL_;
  set filelist;
  file tempsas;
  put '%bio_imp(' filename ');';
run;

options mprint;
%include tempsas /source2;

You can also do it with CALL EXECUTE, but I much prefer writing to a temporary file, which you can then take a look at, and perhaps execute one line at a time when testing at first.

Re: Retrieve the list of files in the configured folder then import these different xlsx files

Gick — Thu, 05 Oct 2023 09:29:23 GMT

Not clear.

Can you put it in a complete code directly. This will allow me to better understand

THANKS.

Re: Retrieve the list of files in the configured folder then import these different xlsx files

Patrick — Thu, 05 Oct 2023 09:46:18 GMT

You can get this warning if there is something wrong with quoting. And once you get into such a situation the code further down the track might no more execute.

In your case for example: You're passing the parameter value in double quote but then also use the macro variable with this value within double quotes - so now you've got double double quotes.

Based on your code below could work for you.

%macro import_files(BIO=, test=N);

    data work.filelist;
      length mem_name $100;
      keep mem_name;

      fid = filename('dir', "&BIO.");
      if fid = 0 then 
        do;
          did = dopen('dir');
          mem_count=coalesce(dnum(did),0);
          do i=1 to mem_count;
            mem_name=dread(did,i);
            if upcase(scan(mem_name,-1,'.'))='XLSX' then output;
          end;
          rc=dclose(did);
        end;
      rc=dclose(fid);
      call symputx('mem_count',mem_count,'l');
    run;

    %if &mem_count>0 %then
      %do;
        filename codegen temp;
        data _null_;
          stop;
          file codegen;
        run;
        data _null_;
          %if %upcase(&test)=Y %then
            %do;
              file print;
            %end;
          %else
            %do;
              file codegen mod;
            %end;
          set work.filelist;
          put
            "proc import datafile='&BIO\" mem_name +(-1) "'" / 
            "  out=work.__temp                             " /
            "  dbms=XLSX replace;                          " /
            "  getnames=yes;                               " /
            "run;                                          " /
                                                             /
            "data work.__Labo_" _n_ z4. ";"                  /
            "  length source_file $100;                    " /
            "  retain source_file '" mem_name +(-1) "';    " /
            "  set work.__temp;                            " /
            "run;                                          " /
            ;
        run;
      
        proc datasets lib=work nolist nowarn;
          delete bdd_labo __labo_: __temp;
        run;quit;

        %include codegen /source2;
        filename codegen clear;

        data work.bdd_labo;
          set work.__labo_:;
        run;

      %end;

%mend;

%import_files(BIO=C:\temp\bio);

Re: Retrieve the list of files in the configured folder then import these different xlsx files

yabwon — Thu, 05 Oct 2023 10:06:23 GMT

You can try out %dirsAndFiles() macro from the BasePlus package:

/* Set up the framework and package */

filename packages "%sysfunc(pathname(work))"; /* setup WORK as temporary directory for packages, or any you find convenient */
filename SPFinit url "https://raw.githubusercontent.com/yabwon/SAS_PACKAGES/main/SPF/SPFinit.sas";
%include SPFinit; /* enable the framework */

%installPackage(SPFinit BasePlus) /* install a package */
%loadPackage(BasePlus)    /* load the package content into the SAS session */

Code to execute:


/* use %dirsAndFiles() macro from basePlus */

/* help info:
%helpPackage(BasePlus,dirsandfiles)
*/

/* get XLSX files */
%dirsAndFiles(C:/SAS_WORK,ODS=work.ListXLSX,fileExt=XLSX);

/* import in the data */
data _null_;
set work.ListXLSX;
call execute('
proc import 
  datafile=' !! quote(catx('/',root,dname,fn)) !! 
' out=WORK.Labo' !! cats(_N_) !!'
	dbms=XLSX replace;
	getnames=yes;
run;
');
run;

/* get the info about variables lengths */
proc contents 
  data=work._all_
  out=work.variables(where=(memname like 'LABO%'))
  noprint;
run;

proc sql noprint;
  select catx(" ", name, ifc(type=2,"$"," "), max(length))
  into :varsList separated by " "
  from work.variables
  group by name
  ;
quit;

%put *TEST: &varsList.*;

/* put all of it together */
data BDD_labo;
 length &varsList.;
 set labo:;
run;

Bart

Re: Retrieve the list of files in the configured folder then import these different xlsx files

Gick — Thu, 05 Oct 2023 10:14:26 GMT

Re: Retrieve the list of files in the configured folder then import these different xlsx files

Gick — Thu, 05 Oct 2023 10:14:53 GMT

What I get with this code

Re: Retrieve the list of files in the configured folder then import these different xlsx files

yabwon — Thu, 05 Oct 2023 10:17:40 GMT

This basically means:

1) there are some unmatched quotes in the code

2) restart SAS session 😉

Bart

Re: Retrieve the list of files in the configured folder then import these different xlsx files

Kurt_Bremser — Thu, 05 Oct 2023 10:25:55 GMT

START A NEW SESSION.

Then, develop your code as suggested, without macro coding at the beginning, and in steps.

Re: Retrieve the list of files in the configured folder then import these different xlsx files

Gick — Thu, 05 Oct 2023 10:33:49 GMT

%macro import_files(BIO=);

data fichiers (keep=fichiers);
length fichiers $256;
fich=filename('fich',"&BIO.");
	did=dopen('fich');
	nb_fich=dnum(did);
	do i=1 TO nb_fich;
		fichiers=dread(did,i);
		output;
	end;
	rc=dclose(did);
run; 


data_null_; SET fichiers;
call symput('Labo'||left(trim(_n_)),fichiers);
call symput('nb',_n_);
run; 


%do l=1 %to %sysfunc(countw(&nb.));
				proc import datafile="&BIO.\&&Labo.&l..xlsx" out=&&Labo.&l.
					dbms=XLSX replace;
				run;  
%end;
			
data BDD_labo; 
   set
   %do i = 1 %to %sysfunc(countw(&nb.));
      Labo&i.
   %end;
   ;
run;

%mend;  

%import_files(BIO=C:\BIO);

Here is a new code proposal. It works but I still get an error message. What I do not understand.

Thanks for your help.
Gick

Re: Retrieve the list of files in the configured folder then import these different xlsx files

Gick — Thu, 05 Oct 2023 10:37:03 GMT

/**here is the log/
NOTE: Line generated by the invoked macro "IMPORT_FILES".
24 data fichiers (keep=fichiers); length fichiers $256; fich=filename('fich',"&BIO."); did=dopen('fich');
24 ! nb_fich=dnum(did); do i=1 TO nb_fich; fichiers=dread(did,i); output; end; rc=dclose(did); run; data_null_;
__________
180
24 ! SET fichiers; * Le nom
ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: Line generated by the invoked macro "IMPORT_FILES".
24 data fichiers (keep=fichiers); length fichiers $256; fich=filename('fich',"&BIO."); did=dopen('fich');
24 ! nb_fich=dnum(did); do i=1 TO nb_fich; fichiers=dread(did,i); output; end; rc=dclose(did); run; data_null_;
24 ! SET fichiers; * Le nom
___
180
ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: Line generated by the invoked macro "IMPORT_FILES".
24 des fichiers; call symput('Labo'||left(trim(_n_)),fichiers);
____
180

ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: Line generated by the invoked macro "IMPORT_FILES".
24 * Le nombre de fichiers; call symput('nb',_n_);
2 Le Système SAS 12:52 Thursday, October 5, 2023

____
180

ERROR 180-322: Statement is not valid or it is used out of proper order.

WARNING: Apparent symbolic reference NB not resolved.
WARNING: Apparent symbolic reference LABO not resolved.
NOTE: Line generated by the macro variable "L".
24 &Labo.1
_
22
200

Re: Retrieve the list of files in the configured folder then import these different xlsx files

Gick — Thu, 05 Oct 2023 10:40:14 GMT

for a new code proposed

Re: Retrieve the list of files in the configured folder then import these different xlsx files

Gick — Thu, 05 Oct 2023 11:03:56 GMT

NOTE: Line generated by the invoked macro "IMPORT_FILES".
68 data fichiers (keep=fichiers); length fichiers $256; fich=filename('fich',"&BIO."); did=dopen('fich');
68 ! nb_fich=dnum(did); do i=1 TO nb_fich; fichiers=dread(did,i); output; end; rc=dclose(did); run; data_null_;
__________
180
68 ! SET fichiers; * Le
ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: Line generated by the invoked macro "IMPORT_FILES".
68 data fichiers (keep=fichiers); length fichiers $256; fich=filename('fich',"&BIO."); did=dopen('fich');
68 ! nb_fich=dnum(did); do i=1 TO nb_fich; fichiers=dread(did,i); output; end; rc=dclose(did); run; data_null_;
68 ! SET fichiers; * Le
___
180
ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: Line generated by the invoked macro "IMPORT_FILES".
68 des fichiers; call symput('Labo'||left(trim(_n_)),fichiers);
____
180

ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: Line generated by the invoked macro "IMPORT_FILES".
68 * Le nombre de fichiers; call symput('nb',_n_);
____
180

ERROR 180-322: Statement is not valid or it is used out of proper order.

WARNING: Apparent symbolic reference NB not resolved.
WARNING: Apparent symbolic reference LABO not resolved.
NOTE: Line generated by the macro variable "L".
68 &Labo.1
_
22
200
WARNING: Apparent symbolic reference LABO not resolved.
NOTE: PROCEDURE IMPORT used (Total process time):
3 Le Système SAS 13:41 Thursday, October 5, 2023

real time 0.00 seconds
cpu time 0.00 seconds

NOTE: The SAS System stopped processing this step because of errors.

ERROR 22-322: Attendu : un nom.

ERROR 200-322: The symbol is not recognized and will be ignored.

WARNING: Apparent symbolic reference NB not resolved.
ERROR: Le fichier WORK.LABO1.DATA n'existe pas.

Re: Retrieve the list of files in the configured folder then import these different xlsx files

Patrick — Thu, 05 Oct 2023 12:11:14 GMT

@Gick The code I've shared is tested and will work as long as the first sheets in your Excels aren't too messy - like a column in one only having cells with digits and though mapping into a SAS numerical variables and then in another Excel the exactly same named column with alphanumeric values mapping into a SAS character variable.

You need to start a NEW SAS SESSION. The error you get is due to unbalanced quotation marks from running some other code in the same SAS session.

Re: Retrieve the list of files in the configured folder then import these different xlsx files

Gick — Thu, 05 Oct 2023 12:22:41 GMT

%macro import_files(BIO=);
/*1**/

data fichiers (keep=fichiers);
length fichiers $256;
fich=filename('fich',"&BIO.");
/* */
did=dopen('fich');
/* compt */
nb_fich=dnum(did);
do i=1 TO nb_fich;
fichiers=dread(did,i);
output;
end;
/* */
rc=dclose(did);
run;

/*2 */
proc sql;
select SUBSTR(fichiers, 1, INDEX(fichiers, '.') - 1) into :Labo_list separated by ' '
from fichiers;
quit;
%put &Labo_list.; /* log */

/*3 -*/
%do l = 1 %to %sysfunc(countw(&Labo_list.));
%let labo = %scan(&Labo_list., &l.);
%put &labo.;
proc import datafile="&BIO.\&labo." out=Labo&l.
dbms=XLSX replace;
run;
%end;

data BDD_labo;
set
%do i = 1 %to %sysfunc(countw(&Labo_list.));
Labo&i.
%end;
;
run;

%mend;

%import_files(BIO=C:\BIO);