DATA Step, Macro, Functions and more

Macro for importing files with no extension

Accepted Solution Solved
Reply
Contributor
Posts: 20
Accepted Solution

Macro for importing files with no extension

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!


Accepted Solutions
Solution
‎03-16-2013 11:08 PM
Super User
Super User
Posts: 6,499

Re: Macro for importing files with no extension

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;

View solution in original post


All Replies
SAS Super FREQ
Posts: 8,743

Re: Macro for importing files with no extension

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


no_extension.pngwith_extension.png
Super User
Super User
Posts: 6,499

Re: Macro for importing files with no extension

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

Contributor
Posts: 20

Re: Macro for importing files with no extension

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.

Solution
‎03-16-2013 11:08 PM
Super User
Super User
Posts: 6,499

Re: Macro for importing files with no extension

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;

Contributor
Posts: 20

Re: Macro for importing files with no extension

Thanks a lot!! That is a great idea!!

Trusted Advisor
Posts: 1,128

Re: Macro for importing files with no extension

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

Thanks,
Jag
☑ This topic is SOLVED.

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

Discussion stats
  • 6 replies
  • 804 views
  • 6 likes
  • 4 in conversation