BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jhhuh
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

6 REPLIES 6
Cynthia_sas
SAS Super FREQ

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
Tom
Super User Tom
Super User

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

jhhuh
Calcite | Level 5

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.

Tom
Super User Tom
Super User

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;

jhhuh
Calcite | Level 5

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

Jagadishkatam
Amethyst | Level 16

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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