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
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.
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;
%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;
Hi, thanks your answer. It is working only for last file. The others not appear.
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?
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
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!*/
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.
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
Thanks the answers. i will try to do that.
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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.