BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Aleixo
Quartz | Level 8

Hi,

 

I am using this code for import files. This code is a copy from another topic.

 

%let path=C:\My_SAS_PROJECTS;
filename folder "&path\Data.";
libname c 'C:\Temp';         /* just an example destination folder */
options validmemname=extend; /* to allow non-standard dataset names */
 
/* Making a list of all files in the folder */
data FilesInFolder;
   length Line 8 File $300;
   List = dopen('folder');  /* corrected the function argument */
   do Line = 1 to dnum(List);
        File = trim(dread(List,Line));
        output;
   end;
   drop list line;
run;
 
/* Creating global macro variables */  /* not "local" */
data _NULL_;
     set FilesInFolder end=final;
     call symput(cats('File', _N_), trim(File));     /* used CATS instead of COMPRESS (...||...) */
     call symput(cats('Name', _N_), trim(nliteral(substr(File,1,min(32, length(File)-4))))); /* inserted */
     if final then call symputx(trim('Total'), _N_); /* replaced symput by symputx */
run;


/* This macro should import all files specified in the list and save them as datasets */
%macro loop;
%do i = 1 %to &Total;
  proc import datafile="&path\Data\&&File&i"
       out=c.&&name&i  /* adapted */
       dbms=xlsx
       replace;
       getnames=yes;
  run;
%end;
%mend loop;
 
%loop

What i want is to named the output with name of file.

 

I am using this function to cut extension file, like xlsx. It is working for xlsx not for all extensions. But ok fine.

 

Nopath = substr(trim(dread(List,Line)), 1, length(trim(dread(List,Line))) -5)

 

It is not working.

 

/* This macro should import all files specified in the list and save them as datasets */
%macro loop;
%do i = 1 %to &Total;
  proc import datafile="&path\Data\&&File&i"
       out=&&Nopath&i  /* adapted */
       dbms=xlsx
       replace;
       getnames=yes;
  run;
%end;
%mend loop;
 
%loop

 

 72 PROC IMPORT DATAFILE= ""&path\Data\&&File&i"  OUT=&&Nopath&i DBMS=XLSX replace; GETNAMES=YES; RUN;

_
76
ERROR 76-322: Syntax error, statement will be ignored.

 

I do not understand why is not working and produce several outputs with name of file.

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Aleixo
Quartz | Level 8

I don´t how but i solved the problem using a previous code.

 

%let path= %sysfunc(pathname(work));
filename folder "&path\";
options validmemname=extend; /* to allow non-standard dataset names */
 
/* Making a list of all files in the folder */
data FilesInFolder;
   length Line 8 File $300;
   List = dopen('folder');  /* corrected the function argument */
   do Line = 1 to dnum(List);
        File = trim(dread(List,Line));
        output;
   end;
   drop list line;
run;

PROC SQL;
   CREATE TABLE WORK.Filtro_de_excel AS 
   SELECT t1.File
      FROM WORK.FilesInFolder t1
      WHERE t1.File CONTAINS '.xlsx';
QUIT;
 
/* Creating global macro variables */  /* not "local" */
data _NULL_;
     set Filtro_de_excel end=final;
     call symput(cats('File', _N_), trim(File));     /* used CATS instead of COMPRESS (...||...) */
     call symput(cats('Name', _N_), trim(nliteral(substr(File,1,min(32, length(File)-4))))); /* inserted */
     if final then call symputx(trim('Total'), _N_); /* replaced symput by symputx */
run;

/* This macro should import all files specified in the list and save them as datasets */
%macro loop;
%do i = 1 %to &Total;
  proc import datafile="&path\&&File&i"
       out=Work.&&name&i  /* adapted */
       dbms=xlsx replace;  
	   getnames=yes;
  run;
%end;
%mend loop;
 
%loop

If someone know why SAS accept this time:

out=Work.&&name&i 

 

i appreciate that.

View solution in original post

11 REPLIES 11
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Your making a mess of code out of a mole hill.  You also have errors in your code such as Data. where the dot is invalid.

filename folder "c:/my_sas_projects/data";                    
libname c 'c:/temp';         
options validmemname=extend;   /* Note, bad idea!! */

data _null_;
   length Line 8 File name $300;
   List = dopen('folder');  
   do Line = 1 to dnum(List);
        File = trim(dread(List,Line));
        name=cats('Name', _N_), trim(nliteral(substr(File,1,min(32, length(File)-4))));
        call execute(cats('proc import datafile="c:/my_sas_projects/data/',file,'" out=c.',name,'  dbms=xlsx replace; getnames=yes; run;');
   end;
run;

 

 

MadhuKorni
Quartz | Level 8
%Macro Import;

filename filesloc "path";

%let dirid = %sysfunc(dopen(filesloc));
%let filescnt = %sysfunc(dnum(dirid));

%do i = 1 %to &filescnt;
%let InputFilename = %sysfunc(dread(&dirid,&i));
data _null_;
FileName = scan(&InputFilename,1,'.');
opdataset=substr(Filename,1,min(32,length(Filename)));*DatasetName should be LE 32 Characters;
call symput("OutputDataset",opdataset);
run;
proc import datafile = "path\&InputFileName" dbms = excel out = &outputdataset replace;
run;
%end;

%Mend;

%Import;
Aleixo
Quartz | Level 8

Hi, thanks your answer. It is working only for last file. The others not appear.

Aleixo
Quartz | Level 8

Sorry. My full code of import is this. 

 

I am using pathname(work) because i use "copy files" to put files in the server.

 

%let namefile = %sysfunc(pathname(work));
%put &namefile;

filename pasta "&namefile";
options validmemname=extend; /* to allow non-standard dataset names */

/* Making a list of all files in the pasta */
data Lista_de_ficheiros;
   length Line 8 File_name $300;
   List = dopen('pasta');  /* corrected the function argument */
   do Line = 1 to dnum(List);
        File_name = cats("&namefile", '\', trim(dread(List,Line)));
		Nopath = substr(trim(dread(List,Line)), 1, length(trim(dread(List,Line))) -5);
output;
   end;
   drop list line;
run;

PROC SQL;
   CREATE TABLE WORK.FILTER_FOR_LISTA_DE_FICHEIR_0000 AS 
   SELECT t1.File_name,
   		   t1.Nopath
      FROM WORK.LISTA_DE_FICHEIROS t1
      WHERE t1.File_name CONTAINS '.xlsx';
QUIT;

data INFORMACAO_IMPORTAR_TESTE;
     set WORK.FILTER_FOR_LISTA_DE_FICHEIR_0000 end=final;
     call symput(cats('File_name', _N_), trim(File_name));     /* used CATS instead of COMPRESS (...||...) */
     call symput(cats('Name', _N_), trim(nliteral(substr(File_name,1,min(32, length(File_name)-4))))); /* inserted */
	 call symput("OutputDataset",Nopath);
     if final then call symputx(trim('Total'), _N_); /* replaced symput by symputx */
RUN;

%macro loop;
 %do i = 1 %to &Total;
PROC IMPORT OUT=&OutputDataset
		DATAFILE= "&&File_name&i"
		DBMS=XLSX replace;
		GETNAMES=YES;
RUN;
%end;
 %mend loop;
 
%loop

No errors but only produce the final file, and the others 4 files are not.

 

What could be the error?

andreas_lds
Jade | Level 19
  1. Remove the loop from the macro.
  2. Add parameters for file_name and outputDataset to the macro.
  3. remove call symput calls from the last datastep
  4. call the macro in that datastep using call execute
Aleixo
Quartz | Level 8

Hi,

 

I do not understand point  2. Add parameters. Can you help me?

 

%let namefile = %sysfunc(pathname(work));
%put &namefile;

filename pasta "&namefile";
options validmemname=extend; /* to allow non-standard dataset names */

/* Making a list of all files in the pasta */
data Lista_de_ficheiros;
   length Line 8 File_name $300;
   List = dopen('pasta');  /* corrected the function argument */
   do Line = 1 to dnum(List);
        File_name = cats("&namefile", '\', trim(dread(List,Line)));
		Nopath = substr(trim(dread(List,Line)), 1, length(trim(dread(List,Line))) -5);
output;
   end;
   drop list line;
run;

PROC SQL;
   CREATE TABLE WORK.FILTER_FOR_LISTA_DE_FICHEIR_0000 AS 
   SELECT t1.File_name,
   		   t1.Nopath
      FROM WORK.LISTA_DE_FICHEIROS t1
      WHERE t1.File_name CONTAINS '.xlsx';
QUIT;

data INFORMACAO_IMPORTAR_TESTE;
     set WORK.FILTER_FOR_LISTA_DE_FICHEIR_0000 end=final;
     call execute(loop);
     if final then call symputx(trim('Total'), _N_); /* replaced symput by symputx */
RUN;

%macro loop;

%let OutputDataset = Nopath;
%let File_name = File_name;
PROC IMPORT OUT=&OutputDataset
		DATAFILE= "&&File_name&i"
		DBMS=XLSX replace;
		GETNAMES=YES;
RUN;
 %mend loop;
 
%loop

I did step 1, 3, and 4.

 

Thanks your attention

 

andreas_lds
Jade | Level 19

Untested code:

 

/* define macro before using it */
%macro loop(FileName, OutputDataset);

   proc import out=&OutputDataset.
      datafile= "&FileName."
      dbms=xlsx replace;
   run;

%mend loop;


data INFORMACAO_IMPORTAR_TESTE;
   set work.filter_for_lista_de_ficheir_0000;

   length _DatasetName $ 42;

   _DatasetName = nliteral(substr(File,1,min(32, length(File)-4)));

   call execute(cats('%loop(', File, ',', _DatasetName, ')'));
run;


/*%loop don't call loop here!*/
ballardw
Super User

With

72 PROC IMPORT DATAFILE= ""&path\Data\&&File&i"  OUT=&&Nopath&i DBMS=XLSX replace; GETNAMES=YES; RUN;

_
76
ERROR 76-322: Syntax error, statement will be ignored.

 

The error is because the option GETNAMES is not valid for DBMS=XLSX (or anything not delimited).

 

From the documentation:

The GETNAMES statement is valid only for delimited files. 

 

BTW extensive reliance on Proc Import to read multiple files means that you will eventually have problems with

1) Same named variables that should be of the same type (numeric or character) ending up with different types causing errors when attempting to combine data sets.

2) Same named character variables with different lengths causing warnings about truncated data when combining data sets and possible loss of data from the truncation

3) Columns that should have the same variable names in the SAS data set ending up with different actual names for source files that should have the same structure.

The above warning acquires more importance when using Excel as a file source.

Aleixo
Quartz | Level 8

Hi,

 

After that you mean that it is impossible to have an output name dinamic?

 

I do not understand why OUT=&&Nopath&i  is not accept by SAS.

 

Thanks

Aleixo
Quartz | Level 8

Thanks the answers. i will try to do that.

Aleixo
Quartz | Level 8

I don´t how but i solved the problem using a previous code.

 

%let path= %sysfunc(pathname(work));
filename folder "&path\";
options validmemname=extend; /* to allow non-standard dataset names */
 
/* Making a list of all files in the folder */
data FilesInFolder;
   length Line 8 File $300;
   List = dopen('folder');  /* corrected the function argument */
   do Line = 1 to dnum(List);
        File = trim(dread(List,Line));
        output;
   end;
   drop list line;
run;

PROC SQL;
   CREATE TABLE WORK.Filtro_de_excel AS 
   SELECT t1.File
      FROM WORK.FilesInFolder t1
      WHERE t1.File CONTAINS '.xlsx';
QUIT;
 
/* Creating global macro variables */  /* not "local" */
data _NULL_;
     set Filtro_de_excel end=final;
     call symput(cats('File', _N_), trim(File));     /* used CATS instead of COMPRESS (...||...) */
     call symput(cats('Name', _N_), trim(nliteral(substr(File,1,min(32, length(File)-4))))); /* inserted */
     if final then call symputx(trim('Total'), _N_); /* replaced symput by symputx */
run;

/* This macro should import all files specified in the list and save them as datasets */
%macro loop;
%do i = 1 %to &Total;
  proc import datafile="&path\&&File&i"
       out=Work.&&name&i  /* adapted */
       dbms=xlsx replace;  
	   getnames=yes;
  run;
%end;
%mend loop;
 
%loop

If someone know why SAS accept this time:

out=Work.&&name&i 

 

i appreciate that.

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!

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
  • 11 replies
  • 3231 views
  • 2 likes
  • 5 in conversation