importing datasets with names in the list

[dustychair]

Hi,

I am trying to import some excel files. I first listed the filenames. Now I am trying to import those files that I listed. I think the last part is not correct but not sure where to edit.

Thanks,

%macro maps(d);
data filenames_&d. ;
length fref $8 fname $200;
did=filename(fref,"Z:/Tests/&d.");
did=dopen(fref);
do i=1 to dnum(did);
	fname=dread(did,i);
output;
end;
did=dclose(did);
did=filename(fref);
keep fname;
run;
data filenames_&d. ;
set  filenames_&d. ; 
I=FIND(fname,"OE");
If substr(fname,I,2) ne ("OE") then delete;
DROP I;
run;

%do j=1 %to &&fname;
data _null_;
set filenames_&d.;
if _n_=&j;
call symput ('fname',fname);
run;

proc import file="Z:/Tests/&d.\&fname"
out=testmap_&d.
dbms=xlsx;
run;
%end;
%mend maps; 
%maps(MAT1I);

[PaigeMiller]

---

@dustychair wrote:

I think the last part is not correct but not sure where to edit.

This is never sufficient. Saying something is "not correct" and providing no additional information about what happens that is "not correct" gives us nothing to go on, and is to be avoided. Explaining and showing us would move things much closer to a solution. Please do this every time from now on. Please do not ever tell us it is "not correct" without explanation and evidence.

If there are errors or other problems in the log, then SHOW US the entire log for this macro. We need to see every single line of the log for this macro, not selected parts. In addition, since this is a macro, please turn on the macro debugging by running this line of code, and then run the macro again.

options mprint;

Please copy the log as text and paste it into the window that appears when you click on the </> icon.

If there are no problems in the log, but the output of the macro is not correct, then SHOW US what you see, and explain what it is that you want to see.

[Tom · Accepted solution]

You have to import each file to a different SAS dataset name, otherwise only either the first or last one will exist (depending on whether or not you added the REPLACE option to the PROC IMPORT code).

You could perhaps just add a numeric suffix to TESTMAP&D. to make the names unique.

If by the "last part" you mean the %DO loop then remove it.

To generate code from data you can just use a data step instead.

data _null_;
  set filenames_&d.;
  call execute(catx(' '
  ,'proc import dbms=xlsx replace file=' 
  ,quote(cats("Z:\Tests\&d.\",fname))
  ,"out=",cats("testmap_&d.",_n_)
  ,'run;'
 ));
run;

[dustychair]

Thank you for your response. Here is what i got.

210
211  %macro maps(d);
212  data filenames_&d. ;
213  length fref $8 fname $200;
214  did=filename(fref,"P:/Contracts/2022 Test Maps Battery Form ID/Test Maps/&d.");
215  did=dopen(fref);
216  do i=1 to dnum(did);
217      fname=dread(did,i);
218  output;
219  end;
220  did=dclose(did);
221  did=filename(fref);
222  keep fname;
223  run;
224  data filenames_&d. ;
225  set  filenames_&d. ;
226  I=FIND(fname,"OE");
227  If substr(fname,I,2) ne ("OE") then delete;
228  DROP I;
229  run;
230
231  data _null_;
232    set filenames_&d.;
233    call execute(catx(' '
234    ,'proc import dbms=xlsx replace file='
235    ,quote(cats("P:/Contracts/2022 Test Maps Battery Form ID/Test Maps\",fname))
236    ,"out=",cats("testmap_&d.",_n_)
237    ,'run;'
238   ));
239  run;
240
241
242  %mend maps;
243  %maps(MAT1I);
MPRINT(MAPS):   data filenames_MAT1I ;
MPRINT(MAPS):   length fref $8 fname $200;
MPRINT(MAPS):   did=filename(fref,"P:/Contracts/2022 Test Maps Battery Form ID/Test Maps/MAT1I");
MPRINT(MAPS):   did=dopen(fref);
MPRINT(MAPS):   do i=1 to dnum(did);
MPRINT(MAPS):   fname=dread(did,i);
MPRINT(MAPS):   output;
MPRINT(MAPS):   end;
MPRINT(MAPS):   did=dclose(did);
MPRINT(MAPS):   did=filename(fref);
MPRINT(MAPS):   keep fname;
MPRINT(MAPS):   run;

NOTE: The data set WORK.FILENAMES_MAT1I has 7 observations and 1 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds


MPRINT(MAPS):   data filenames_MAT1I ;
MPRINT(MAPS):   set filenames_MAT1I ;
MPRINT(MAPS):   I=FIND(fname,"OE");
MPRINT(MAPS):   If substr(fname,I,2) ne ("OE") then delete;
MPRINT(MAPS):   DROP I;
MPRINT(MAPS):   run;

NOTE: Invalid second argument to function SUBSTR at line 659 column 72.
fname=PTM_PC_PARCC_SPRING19ABO_P_MATH_Gr9_MAT1I_19M1HSSFPEB1000001_P02.XLSX I=0 _ERROR_=1 _N_=1
NOTE: Invalid second argument to function SUBSTR at line 659 column 72.
fname=PTM_PC_PARCC_SPRING21ABO_E_MATH_Gr9_MAT1I_21M1HSSFAEB1000001_P02.XLSX I=0 _ERROR_=1 _N_=2
NOTE: Invalid second argument to function SUBSTR at line 659 column 72.
fname=PTM_PC_PARCC_SPRING21ABO_E_MATH_Gr9_MAT1I_21M1HSSFHEB1000001_P02.XLSX I=0 _ERROR_=1 _N_=3
NOTE: Invalid second argument to function SUBSTR at line 659 column 72.
fname=PTM_PC_PARCC_SPRING21ABO_E_MATH_Gr9_MAT1I_21M1HSSFNEB1000001_P02.XLSX I=0 _ERROR_=1 _N_=4
NOTE: Invalid second argument to function SUBSTR at line 659 column 72.
fname=PTM_PC_PARCC_SPRING21ABO_E_MATH_Gr9_MAT1I_21M1HSSFREB1000001_P02.XLSX I=0 _ERROR_=1 _N_=6
NOTE: Invalid second argument to function SUBSTR at line 659 column 72.
fname=PTM_PC_PARCC_SPRING21ABO_E_MATH_Gr9_MAT1I_21M1HSSFTEB1000001_P02.XLSX I=0 _ERROR_=1 _N_=7
NOTE: There were 7 observations read from the data set WORK.FILENAMES_MAT1I.
NOTE: The data set WORK.FILENAMES_MAT1I has 1 observations and 1 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds


MPRINT(MAPS):   data _null_;
MPRINT(MAPS):   set filenames_MAT1I;
MPRINT(MAPS):   call execute(catx(' ' ,'proc import dbms=xlsx replace file='
,quote(cats("P:/Contracts/2022 Test Maps Battery Form ID/Test Maps\",fname))
,"out=",cats("testmap_MAT1I",_n_) ,'run;' ));
MPRINT(MAPS):   run;

MPRINT(MAPS):   proc import dbms=xlsx replace file= "P:/Contracts/2022 Test Maps Battery Form ID/Test
Maps\PTM_PC_PARCC_SPRING21ABO_E_MATH_Gr9_MAT1I_21M1HSSFOEB1000001_P02.XLSX" out= testmap_MAT1I1 run;
NOTE: There were 1 observations read from the data set WORK.FILENAMES_MAT1I.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


NOTE: CALL EXECUTE generated line.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

NOTE: The SAS System stopped processing this step because of errors.
NOTE: Line generated by the CALL EXECUTE routine.
1   + proc import dbms=xlsx replace file= "P:/Contracts/2022 Test Maps Battery Form ID/Test
1  !+Maps\PTM_PC_PARCC_SPRING21ABO_E_MATH_Gr9_MAT1I_21M1HSSFOEB1000001_P02.XLSX" out= testmap_MAT1I1
1  !+run;
     ---
     22
     202
ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATAFILE, DATATABLE, DBMS, DEBUG,
              FILE, OUT, REPLACE, TABLE, _DEBUG_.

ERROR 202-322: The option or parameter is not recognized and will be ignored.

244  options mprint;

%macro maps(d);
data filenames_&d. ;
length fref $8 fname $200;
did=filename(fref,"P:/Contracts/2022 Test Maps Battery Form ID/Test Maps/&d.");
did=dopen(fref);
do i=1 to dnum(did);
	fname=dread(did,i);
output;
end;
did=dclose(did);
did=filename(fref);
keep fname;
run;
data filenames_&d. ;
set  filenames_&d. ; 
I=FIND(fname,"OE");
If substr(fname,I,2) ne ("OE") then delete;
DROP I;
run;

data _null_;
  set filenames_&d.;
  call execute(catx(' '
  ,'proc import dbms=xlsx replace file=' 
  ,quote(cats("P:/Contracts/2022 Test Maps Battery Form ID/Test Maps\",fname))
  ,"out=",cats("testmap_&d.",_n_)
  ,'run;'
 ));
run;


%mend maps; 
%maps(MAT1I);
options mprint;

[PaigeMiller]

1   + proc import dbms=xlsx replace file= "P:/Contracts/2022 Test Maps Battery Form ID/Test
1  !+Maps\PTM_PC_PARCC_SPRING21ABO_E_MATH_Gr9_MAT1I_21M1HSSFOEB1000001_P02.XLSX" out= testmap_MAT1I1
1  !+run;
     ---
     22
     202
ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATAFILE, DATATABLE, DBMS, DEBUG,
              FILE, OUT, REPLACE, TABLE, _DEBUG_.

Your log makes it clear you are missing a semicolon. The code shown will not run until you add in the semi-colon.

[dustychair]

I see but i don't know where to put the semicolon. I tried many locations but did not work.

[SASKiwi]

Learn to use the SAS documentation. As I'm sure you realise, guessing is not productive.

First, I suggest you bookmark the above web link.

Second, once you are at the SAS documention main page, select the SAS 9.4 programming option: https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/pgmsashome/home.htm

Third, select the search option and type in PROC IMPORT and press search. You should soon be able to find what you are looking for.

Hint: The RUN statement is a separate statement in SAS, telling it to execute the preceding code. Place a semicolon just before RUN like this ==> ;run;

This will close any previous statement.

Even bigger hint - see what I've done on line 237.

231  data _null_;
232    set filenames_&d.;
233    call execute(catx(' '
234    ,'proc import dbms=xlsx replace file='
235    ,quote(cats("P:/Contracts/2022 Test Maps Battery Form ID/Test Maps\",fname))
236    ,"out=",cats("testmap_&d.",_n_)
237    ,';run;'
238   ));
239  run;

[PaigeMiller]

---

@dustychair wrote:

I see but i don't know where to put the semicolon. I tried many locations but did not work.

---

First, you need to figure out where the semi-colon goes to make the code work, without macros. The code produced as shown in the log is

proc import dbms=xlsx replace file= 
"P:/Contracts/2022 Test Maps Battery Form ID/TestMaps\PTM_PC_PARCC_SPRING21ABO_E_MATH_Gr9_MAT1I_21M1HSSFOEB1000001_P02.XLSX" 
out= testmap_MAT1I1
run;

Where do you put the semicolon to make the above work?

[ballardw]

First thing, your Fname will include values that are the names of subfolders if there are any. If any of those happen to have "OE" in the names then bad things are expected.

You have a couple of potential problems with this:

I=FIND(fname,"OE");
If substr(fname,I,2) ne ("OE") then delete;

First is case of the returned fname values. Windows filenames can be mixed case. So you will not find "oE", "oe" or "Oe" spellings that the OS will return.

Second, if not found the Find function returns 0. The Substr function will complain about invalid parameters and the results are problematied for the IF in that case. Since you are not actually using I anywhere else you could use

if find(fname,"OE",'i') = 0 then delete.

The 'i' parameter tells Find to do case insensitive comparisons, then deletes when not found.

Next, you apparently expect to import XLSX files. Your code does not check for the existence much less the value of any extensions.

A general comment about Proc Import and multiple XLSX files in general: Import guesses properties for columns for every single separate file. So the same column in different import results may be of a different type or length. These two can cause serious problems if you ever intend to combine the data. If your data sources are as messy as some spreadsheets then you can even have issues with the variable names changing that you expect to be the same in multiple files.

[Patrick]

@dustychair Here some code similar to what you've done but without the need of macro coding. You can of course still "wrap" a macro around it if you want to make it a re-usable autocall macro.

%let path=c:\temp;
%let ext=xlsx;
%let exclusion_string=;
%let inclusion_string=oe;

/* create directory listing */
/* code based on: https://www.lexjansen.com/wuss/2012/55.pdf */
data dirlist;
  keep file_nm;
  length fref $8 file_nm $80;
  rc = filename(fref, "&path");

  if rc = 0 then
    do;
      did = dopen(fref);
      rc = filename(fref);
    end;
  else
    do;
      length msg $200.;
      msg = sysmsg();
      put msg=;
      did = .;
    end;

  if did <= 0 then
    putlog 'ERR' 'OR: Unable to open directory.';
  dnum = dnum(did);

  do i = 1 to dnum;
    file_nm = dread(did, i);

    /* If this entry is a file, then output. */
    fid = mopen(did, file_nm);

    if fid > 0 then
      do;
        if "&ext"="" or lowcase(scan(file_nm,-1,'.'))=%lowcase("&ext") 
          then
          do;
            output;
          end;
      end;
  end;

  rc = dclose(did);
run;

/* create valid SAS table names and ensure names are unique */
data out_tables;
  set dirlist;
  if "&exclusion_string" ne " " and find(file_nm,"&exclusion_string",'i')>0 then delete;
  if "&inclusion_string" ne " " and find(file_nm,"&inclusion_string",'i')<=0 then delete;

  if "&ext" = " " then outname=file_nm;
  else outname=substr(file_nm,1,sum(length(file_nm),-length(".&ext")));
  outname=prxchange('s/(^[0-9])/_$1/i',-1,strip(outname));
  outname=prxchange('s/[^0-9a-z]+/_/i',-1,strip(outname));
  outname=substrn(outname,1,32);
  outname=lowcase(outname);
run;

proc sort data=out_tables;
  by outname;
run;

data out_tables_2;
  set out_tables;
  by outname;
  if first.outname then _cnt=1;
  if not (first.outname and last.outname) then
    do;
      outname=cats(substrn(outname,1,30),'_',put(_cnt,f1. -l));
      _cnt+1;     
    end;
run;

/* import Excel files */
%let sv_validvarname=%sysfunc(getoption(validvarname,keyword));
options validvarname=v7;
data _null_;
  set out_tables_2;
  length cmd $500;
  cmd=
    catt
      (
        "proc import file='&path/",file_nm,"'",
        " out=",outname,
        " dbms=xlsx replace;",
        "run;"
      );

  call execute(cmd);

run;
options &sv_validvarname;

[dustychair]

@Patrick Thanks. It is working but it is pulling all of the excel files. I want only excel files which has "OE" in the name. It needs to be mofified.

[Patrick]

---

@dustychair wrote:

@Patrick Thanks. It is working but it is pulling all of the excel files. I want only excel files which has "OE" in the name. It needs to be mofified.

---

I sort-of feel that's a simple modification you could have added yourself ...but o.k., code previously posted amended with additional requirement.

Please note: String exclusions/inclusions are NOT case sensitive. IF you want them case sensitive then remove option 'i' in the find command.

[dustychair]

@Patrick Thank you. I will do that!