Question about %Let

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

Question about %Let

Hi all,

I am managing a dataset and would love to ask the system to take in files containing names ["2014-04-07"] or  ["2014-04-08"] or ["2014-04-09"] or ...  or ["2014-04-14 "] at one go;

At the moment the code is allowing me to upload one file at a time by having a %Let panel: %let xyz = "2014-04-07"; but I wonder if there is a way to allow the %let to include all [date]s in one go?

Many thanks in advance!

Here is the full code...

option compress=yes;

/*Read in all the data in SAS Input or Raw Folder*/

*path to where files are stored;

%let mydir=K:\Clients\...\SAS\INPUT DATA\...\ 2012 May Onward;

/*DOS command to get a list of all data files programs */

        filename mylist pipe "%str(dir %"&mydir%") /b" lrecl=5000;

/*put the list of files into a data set for macro processing*/

data mylist;

        infile mylist truncover;

                input filename $char100.;

        put _infile_;

run;

quit;

%let xyz = "2014-04-07";

data mylist;

set mylist;

if index(upcase(filename),"CSV") = 0 then delete;

if index(upcase(filename),&xyz) = 0 then delete;

run;

data mylist; set mylist;

   call symput ('list_num',_n_);

run;

quit;

/*add advertiser*/

%macro infiledata;

*loop through all files in "dirlist" data set;

%do j=1 %to &list_num;

*if observation=j then pull out the path and filename;

        data _null_;

                set mylist;

                        if _n_=&j;

                        call symput ('filename',trim(left(filename)));

run;


Accepted Solutions
Solution
‎04-15-2014 10:12 AM
Super User
Super User
Posts: 7,039

Re: Question about %Let

You can build the DIR command from the MYDIR and INCLUDE lists by tacking on the wildcards.

I recommend telling the users to put quotes around the value of MYDIR if it contains any spaces.

I recommend using a character that cannot occur in a filename such as | as the delimiter in the list of search terms.

The example below will work for your case, but would need extra quotes if the patterns were allowed to contain spaces.

Example:

%let mydir="c:\downloads";

%let include=test1|class ;

%let search=&mydir\*%sysfunc(tranwrd(&include,|,* &mydir\*))* ;

data _null_;

  infile %sysfunc(quote(dir &search /b)) pipe ;

  input;

  put _infile_;

run;

NOTE: The infile "dir ""c:\downloads""\*test1* ""c:\downloads""\*class* /b" is:

      Unnamed Pipe Access Device,

      PROCESS=dir "c:\downloads"\*test1* "c:\downloads"\*class* /b,

      RECFM=V,LRECL=32767

test1.xls

test1.xlsx

class.xls

NOTE: 3 records were read from the infile "dir ""c:\downloads""\*test1*

      ""c:\downloads""\*class* /b".

      The minimum record length was 9.

      The maximum record length was 10.

View solution in original post


All Replies
Super User
Posts: 5,424

Re: Question about %Let

When having a SAS data set with the file names, explore call execute, probably it would fit your needs.

Data never sleeps
Super User
Posts: 19,770

Re: Question about %Let

You can use wildcards...what are you trying to do overall?

Occasional Contributor
Posts: 12

Re: Question about %Let

Hi Reeza,

This is going to be a repeated process so I want to create a manual panel that I can adjust which file(s) to read in each time I have to run the code. Hope this helps explaining.

Could you also elaborate on wildcards as well please?

Thanks

Will

Super User
Posts: 19,770

Re: Question about %Let

And the date is anywhere in the file name, not just at the end or somewhere? Any naming conventions to the file names?

Occasional Contributor
Posts: 12

Re: Question about %Let

@ Reeza: Sadly yes the date could be anywhere on the file names due to management issues... we can only have them to agree to keep a fixed date format "YYYY-MM-DD" in all files.

Super User
Super User
Posts: 7,039

Re: Question about %Let

Looks like this is reading list of filenames into a dataset. It is getting the list of file names by using the DOS command DIR.

What do you want it to differently when given an input date?  Limit the list to those that match exactly? To those that include that date in the filename?

What is it that you want to do with the filename (or names) once you have them?

Looks like from the code that you want to EXCLUDE the names that match the values in the macro variable.  So let's call that macro variable EXCLUDE.

/*Read in all the data in SAS Input or Raw Folder*/

%let mydir=K:\Clients\...\SAS\INPUT DATA\...\ 2012 May Onward;

%let exclude="2014-04-07" "2014-04-08" ;

data mylist;

  infile %sysfunc(quote(dir "&mydir" / b)) pipe truncover;

  input filename $char256.;

* Eliminate CSV files ;

  if lowcase(scan(filename,-1,'.'))='csv' then delete;

* Eliminate any filename that contain exclude strings ;

  retain exclude %sysfunc(quote(&exclude)) ;

  do i=1 by 1 while (scan(exclude,i,' ','q')^=' ');

    if index(upcase(filename),upcase(dequote(scan(exclude,i,' ','q'))))

       then delete ;

  end;

run;

Trusted Advisor
Posts: 3,211

Re: Question about %Let

VVil,

As you probably able to think in some dir command, using the * and ? wildcarding is possible the easiest way (small files) as you can retrieve the real filename when processing those lists.

SAS(R) 9.4 Companion for Windows, Third Edition  (input referencing files)

With Unix you could have use PRX patterns like style SAS(R) 9.4 Companion for UNIX Environments, Third Edition that would obviously looks more like your request.

Now is looking you want to process the last 7-days marked by naming of those files. If that is the case a loop for working on those 7 date intervals as one run sound the most appropriate.

You could use a standard reference date to be set for that.

---->-- ja karman --<-----
PROC Star
Posts: 7,467

Re: Question about %Let

I still don't know if you are trying to include or exclude files from your secondary list, but an example use of a wild card might be:

filename mylist pipe "dir ""&mydir.\*.sas7bdat"" /b";

which would only include SAS datasets

Occasional Contributor
Posts: 12

Re: Question about %Let

Hi Arthur,

I am trying to include files from secondary list only, thanks.

I am currently trying to apply Tom's logic (in an inclusive rather than exclusive way) onto my code at the moment,

Super User
Super User
Posts: 7,039

Re: Question about %Let

/*Read in all the data in SAS Input or Raw Folder*/

%let mydir=K:\Clients\...\SAS\INPUT DATA\...\ 2012 May Onward;

%let include="2014-04-07" "2014-04-08" ;

data mylist;

  infile %sysfunc(quote(dir "&mydir" / b)) pipe truncover;

  input filename $char256.;

* Eliminate CSV files ;

  if lowcase(scan(filename,-1,'.'))='csv' then delete;

* Eliminate any filename that contain exclude strings ;

  retain include %sysfunc(quote(&include)) ;

  keep=0;

  do i=1 by 1 while (scan(include,i,' ','q')^=' ' and not keep);

    if index(upcase(filename),upcase(dequote(scan(include,i,' ','q'))))

       then keep=1 ;

  end;

  if keep ;

run;

PROC Star
Posts: 7,467

Re: Question about %Let

Then I think you are making it far more difficult than it needs to be.  I'm confused as to whether these are SAS datasets because, unless you are using the validvarname=any option, the names have to begin with either an underscore or a letter, and can only include letters, numbers and underscores.

In the following example I used file names like _20140407

libname myfiles "K:\Clients\...\SAS\INPUT DATA\...\ 2012 May Onward";

data wantlist;

  informat fname $15.;

  input fname;

  cards;

_20140407

_20140408

_20140409

_20140410

;

proc sql noprint;

  select 'myfiles.'||strip(fname)

    into :fnames

      separated by ' '

        from wantlist

  ;

quit;

data want;

  set &fnames.;

run;

Solution
‎04-15-2014 10:12 AM
Super User
Super User
Posts: 7,039

Re: Question about %Let

You can build the DIR command from the MYDIR and INCLUDE lists by tacking on the wildcards.

I recommend telling the users to put quotes around the value of MYDIR if it contains any spaces.

I recommend using a character that cannot occur in a filename such as | as the delimiter in the list of search terms.

The example below will work for your case, but would need extra quotes if the patterns were allowed to contain spaces.

Example:

%let mydir="c:\downloads";

%let include=test1|class ;

%let search=&mydir\*%sysfunc(tranwrd(&include,|,* &mydir\*))* ;

data _null_;

  infile %sysfunc(quote(dir &search /b)) pipe ;

  input;

  put _infile_;

run;

NOTE: The infile "dir ""c:\downloads""\*test1* ""c:\downloads""\*class* /b" is:

      Unnamed Pipe Access Device,

      PROCESS=dir "c:\downloads"\*test1* "c:\downloads"\*class* /b,

      RECFM=V,LRECL=32767

test1.xls

test1.xlsx

class.xls

NOTE: 3 records were read from the infile "dir ""c:\downloads""\*test1*

      ""c:\downloads""\*class* /b".

      The minimum record length was 9.

      The maximum record length was 10.

🔒 This topic is solved and locked.

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

Discussion stats
  • 12 replies
  • 506 views
  • 6 likes
  • 6 in conversation