DATA Step, Macro, Functions and more

Import files with dynamic output

Accepted Solution Solved
Reply
Contributor
Posts: 69
Accepted Solution

Import files with dynamic output

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


Accepted Solutions
Solution
4 weeks ago
Contributor
Posts: 69

Re: Import files with dynamic output

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


All Replies
Super User
Super User
Posts: 9,599

Re: Import files with dynamic output

[ Edited ]

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;

 

 

Frequent Contributor
Posts: 77

Re: Import files with dynamic output

%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;
Contributor
Posts: 69

Re: Import files with dynamic output

Posted in reply to MadhuKorni

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

Contributor
Posts: 69

Re: Import files with dynamic output

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?

Valued Guide
Posts: 574

Re: Import files with dynamic output

  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
Contributor
Posts: 69

Re: Import files with dynamic output

Posted in reply to andreas_lds

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

 

Valued Guide
Posts: 574

Re: Import files with dynamic output

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!*/
Super User
Posts: 13,542

Re: Import files with dynamic output

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.

Contributor
Posts: 69

Re: Import files with dynamic output

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

Contributor
Posts: 69

Re: Import files with dynamic output

Thanks the answers. i will try to do that.

Solution
4 weeks ago
Contributor
Posts: 69

Re: Import files with dynamic output

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 138 views
  • 2 likes
  • 5 in conversation