topic Re: Macro for importing files with no extension in SAS Programming

Macro for importing files with no extension

jhhuh — Sun, 17 Mar 2013 00:25:37 GMT

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!

Re: Macro for importing files with no extension

Cynthia_sas — Sun, 17 Mar 2013 01:23:13 GMT

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

Re: Macro for importing files with no extension

Tom — Sun, 17 Mar 2013 02:13:19 GMT

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

Re: Macro for importing files with no extension

jhhuh — Sun, 17 Mar 2013 02:51:59 GMT

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.

Re: Macro for importing files with no extension

Tom — Sun, 17 Mar 2013 03:08:01 GMT

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;

Re: Macro for importing files with no extension

Jagadishkatam — Sun, 17 Mar 2013 03:26:55 GMT

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

Re: Macro for importing files with no extension

jhhuh — Sun, 17 Mar 2013 03:37:02 GMT

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