Hi all,
I am trying to make a macro which reads in all files in a directory and I successfully bring files with extension.
However, my macro does not work for files with no extension.
Here is the list of files in my list.
dem
beer_da
beer_ga
beer_panel_a.dat
beer_panel_b.dat
beer_panel_c.dat
As you can see above, dem, beer_da and beer_ga do not have any extension.
I can bring them into SAS using infiile statement.
The macro that I have is
/**** panel ****/
%let subdir=/data/jin/first/beer ;
filename dir "&subdir/*.dat ";
data new;
length filename fname $ 200;
infile dir eof=last filename=fname ;
input;
last: filename=fname;
run;
proc sort data=new nodupkey;
by filename;
run;
proc print data=new;
run;
data x;
set new end=last;
call symputx(cats('filename',_n_),filename);
call symputx(cats('dsn',_n_),compress(scan(filename,-1,'/'),'.dat'));
if last then call symputx('nobs',_n_);
run;
%macro readin;
%do i=1 %to &nobs;
data &&dsn&i;
infile "&&filename&i"lrecl=1000 dlm=',' firstobs=2 truncover dsd;
input PANID$ WEEK MINUTE UNITS OUTLET$ DOLLARS IRI_KEY COLUPC 13;
run;
%end;
%mend readin;
%readin;
This successfully worked, but other files... I have no idea.
Does anyone have a good idea?
Thanks in advance!
Let's assume that the part of the program that is generating the names of the files into the macro variables does not change. Then you need logic in the macro to generate different SAS code depending on the name of the file. Here is one way you can do that. The code below generates different INPUT statements depending on the filename. It uses the %SCAN() function to eliminate the path from the filename. I used both / and \ as the delimiters for %SCAN() so it should work on either unix or windows. You might want to add %lowcase() function into the test just in case filename could be "DE" or "De" instead of "de".
%macro readin;
%do i=1 %to &nobs;
data &&dsn&i;
infile "&&filename&i" lrecl=1000 dlm=',' firstobs=2 truncover dsd;
%if "%scan(&&filename&i,-1,/\)"="de" %then %do;
input <<< variables for first file type >>> ;
%end;
%else %if "%scan(&&filename&i,-1,/\)"="beer_da" %then %do;
input <<< variables for second file type >>> ;
%end;
%else %do;
input PANID $ WEEK MINUTE UNITS OUTLET $ DOLLARS IRI_KEY COLUPC 13;
%end;
run;
%end;
%mend readin;
Hi:
I think the issue is that you have hardcoded .DAT in your FILENAME statement. Here's an example using the path, with and without an extension in an INFILE statement. Perhaps this will indicate one possible solution, using a macro variable for the extension. I wasn't sure whether a resolved file reference like this:
"c:\temp\no_ext." (with a dot, but no extension) and it seems to work OK.
cynthia
What is not working?
It does not look like you even tried to read the files that did not have .dat extension.
%let subdir=/data/jin/first/beer ;
filename dir "&subdir/*.dat ";
Apparently I asked wrong question.
(English is not my native language, so please understand.)
There are three different types of data in the file.
(type1 - de / type2- beer_da and ga/ type3- beer_panel_a.dat, beer_panel_b.dat and beer_panel_c.dat)
Each group of data have different variables.
So if I read in data by writing
%let subdir=/data/jin/first/beer ;
filename dir "&subdir/";
and use the infile statement above,
I read in all the data in the file with the same input statement, so I read in some of data wrong.
So.. I need to use different infile statement for each group.
Therefore, I need some SAS code which can read allocate different input statement to each group of data.
That was my problem.
Let's assume that the part of the program that is generating the names of the files into the macro variables does not change. Then you need logic in the macro to generate different SAS code depending on the name of the file. Here is one way you can do that. The code below generates different INPUT statements depending on the filename. It uses the %SCAN() function to eliminate the path from the filename. I used both / and \ as the delimiters for %SCAN() so it should work on either unix or windows. You might want to add %lowcase() function into the test just in case filename could be "DE" or "De" instead of "de".
%macro readin;
%do i=1 %to &nobs;
data &&dsn&i;
infile "&&filename&i" lrecl=1000 dlm=',' firstobs=2 truncover dsd;
%if "%scan(&&filename&i,-1,/\)"="de" %then %do;
input <<< variables for first file type >>> ;
%end;
%else %if "%scan(&&filename&i,-1,/\)"="beer_da" %then %do;
input <<< variables for second file type >>> ;
%end;
%else %do;
input PANID $ WEEK MINUTE UNITS OUTLET $ DOLLARS IRI_KEY COLUPC 13;
%end;
run;
%end;
%mend readin;
Thanks a lot!! That is a great idea!!
Hi,
why don't you add extra parameters into the macro what you have created. this will help you to control the datatype what you are reading and also the variable name;
please try the below macro with additional parameters included for infile and input statements
%macro readin(infile=,input=);
%do i=1 %to &nobs;
data &&dsn&i;
infile &infile;
input &input;
run;
%end;
%mend readin;
%readin(infile="&&filename&i"lrecl=1000 dlm=',' firstobs=2 truncover dsd, input=PANID$ WEEK MINUTE UNITS OUTLET$ DOLLARS IRI_KEY COLUPC 13 );
Hope this helps.
Thanks,
Jagadish
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.