topic Re: Import files with dynamic output in SAS Programming

Import files with dynamic output

Aleixo — Wed, 20 Jun 2018 09:46:11 GMT

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

Re: Import files with dynamic output

RW9 — Wed, 20 Jun 2018 10:08:39 GMT

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;

Re: Import files with dynamic output

MadhuKorni — Wed, 20 Jun 2018 10:15:06 GMT

%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;

Re: Import files with dynamic output

Aleixo — Wed, 20 Jun 2018 11:13:07 GMT

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

Re: Import files with dynamic output

Aleixo — Wed, 20 Jun 2018 11:18:36 GMT

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?

Re: Import files with dynamic output

andreas_lds — Wed, 20 Jun 2018 11:58:24 GMT

Remove the loop from the macro.
Add parameters for file_name and outputDataset to the macro.
remove call symput calls from the last datastep
call the macro in that datastep using call execute

Re: Import files with dynamic output

ballardw — Wed, 20 Jun 2018 14:29:41 GMT

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.

Re: Import files with dynamic output

Aleixo — Thu, 21 Jun 2018 08:24:34 GMT

Thanks the answers. i will try to do that.

Re: Import files with dynamic output

Aleixo — Fri, 22 Jun 2018 08:32:09 GMT

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

Re: Import files with dynamic output

Aleixo — Fri, 22 Jun 2018 08:41:59 GMT

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

Re: Import files with dynamic output

andreas_lds — Fri, 22 Jun 2018 09:13:25 GMT

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!*/

Re: Import files with dynamic output

Aleixo — Fri, 22 Jun 2018 09:29:53 GMT

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.