topic Re: Create macro variables from file names in SAS Programming

Create macro variables from file names

David_Billa — Thu, 10 Dec 2020 13:47:26 GMT

I've the below list of files in one folder. Now I want to create macro variables based on the last four digits. of each file.

INS_GT_1_1000.csv
INS_GT_1_1001.csv
INS_GT_1_1002.csv

I will define the folder name in macro variable as below. Now I want to scan this directory and get the last four digits of each file and store it in macro variables. For each file, I need to create a macro variable which has the value of four digits from the file name.

%let dir=/var/data/Server/

Excepted Results:

First macro variable (e.g. ID1) should have value as 1000, Second macro variable (e.g. ID2) should have value as 1001 and Third macro variable (e.g. ID3) should have value as 1002

Any help?

Re: Create macro variables from file names

PeterClemmensen — Thu, 10 Dec 2020 13:59:55 GMT

Would this include CSV files only?

And what if a file does not end with a digit?

Re: Create macro variables from file names

Shmuel — Thu, 10 Dec 2020 14:13:04 GMT

I suppose this post is a continuation or an alternative way to solve your issue posted in

https://communities.sas.com/t5/SAS-Programming/Conditionally-move-the-files-based-on-macro-variable/m-p/705017

I don't think this alternative will be easier or shorter to do the desired work.

Re: Create macro variables from file names

David_Billa — Thu, 10 Dec 2020 14:15:54 GMT

Only CSV files will be placed in the folder and it will have digits.

Re: Create macro variables from file names

David_Billa — Thu, 10 Dec 2020 14:16:52 GMT

Only CSV files will be placed in the Folder and it will have Digits.

Re: Create macro variables from file names

Kurt_Bremser — Thu, 10 Dec 2020 14:20:46 GMT

Use the file functions to retrieve the filenames, then some string functions to extract the digits:

%let dir=/var/data/Server/;

data files;
fref = "DIR";
if filename(fref,"&dir.") = 0
then do;
  did = dopen(fref);
  if did ne 0
  then do;
    do i = 1 to dnum(did);
      name = dread(did,i);
      if upcase(scan(name,-1,".")) = "CSV" then output;
    end;
    did = dclose(did);
  end;
  rc = filename(fref);
end;
keep name;
run;

data _null_;
set files;
digits = substr(name,length(name)-7,4);
call symputx(cats("id",_n_),digits);
run;

Re: Create macro variables from file names

David_Billa — Thu, 10 Dec 2020 14:52:54 GMT

Thank you. May I know why you have used the variable 'fref' in your
program?

Re: Create macro variables from file names

Kurt_Bremser — Thu, 10 Dec 2020 15:01:41 GMT

It's a matter of habit; when FILENAME is used in a macro, it HAS to be a macro variable and not a string. So I also do the same in a data step.

By using an empty variable of length 8, SAS will assign a name automatically.

Re: Create macro variables from file names

David_Billa — Thu, 10 Dec 2020 16:25:27 GMT

I have one more question for you. I want to use the created macro variables (ID1 - IDn) in other macro under %index function.

Like this,

%index(file_name,‹macrovariable ID1 - IDn>) > 0 then %do
.
.
%end;

May I know how should I use the created macro variables under %index function?

Re: Create macro variables from file names

Tom — Thu, 10 Dec 2020 16:35:42 GMT

You can reference a macro variable's value by writing the name of the macro variable prefixed with the & character.

What is it you are actually trying to do? Perhaps the macro variables are not even required to do what you want.

Re: Create macro variables from file names

David_Billa — Thu, 10 Dec 2020 16:42:34 GMT

Appreciate if you could help me with example for this, 'You can reference a macro variable's value by writing the name of the macro variable prefixed with the & character.'

Re: Create macro variables from file names

Tom — Thu, 10 Dec 2020 16:51:10 GMT

So if you create a macro variable named FILE and want to use the value of FILE in your program you type &FILE. You can add a period at the end of the name to be explicit about where the name ends.

Here is a simple example:

%let name=filename1.xls ;
%let number=1 ;
%if %index(&name,&number) %then %do;
  %put &number is contained in &name.. ;
%end;

Re: Create macro variables from file names

Kurt_Bremser — Thu, 10 Dec 2020 17:08:13 GMT

So you do not need a list of macro variables, but a macro variable with a value, which you want to search for in whatever is represented by file_name here.

@David_Billa wrote:

I have one more question for you. I want to use the created macro variables (ID1 - IDn) in other macro under %index function.

Like this,
%index(file_name,‹macrovariable ID1 - IDn>) > 0 then %do
.
.
%end;
May I know how should I use the created macro variables under %index function?

Re: Create macro variables from file names

David_Billa — Thu, 10 Dec 2020 17:16:00 GMT

@Tom @Kurt_Bremser

I need a need a list of macro variables, but a macro variable with a value and b macro variable with b value and so on.

So how to pass these macro variables as arguments to %index function?

Re: Create macro variables from file names

Kurt_Bremser — Thu, 10 Dec 2020 17:21:38 GMT

You start with a bunch of year/month values, and you want to compare those with another value; please describe what you want to achieve here, the big picture, so to say.

Re: Create macro variables from file names

David_Billa — Thu, 10 Dec 2020 17:39:54 GMT

We are near to the answer I believe. After we create the list macro
variables from your initial program, how to use those macro variables
dynamically as arguments in %index function?

For example, I have the files in one folder as I mentioned in my post, I
want to check if the file has a string which matches with our values of our
macro variables. If it matches then move the files to another folder.

Hope it helps.

Re: Create macro variables from file names

Kurt_Bremser — Thu, 10 Dec 2020 17:43:52 GMT

Create two datasets: one with the filenames, the other with the periods you're looking for. Join them, and use the result to do the file copy. No macro variables and no macro code needed at all.

Re: Create macro variables from file names

David_Billa — Thu, 10 Dec 2020 17:53:54 GMT

I want to do it in a macro as I want to update the existing program which
has macros. I was not allowed to use other than macros.

Re: Create macro variables from file names

ballardw — Thu, 10 Dec 2020 18:24:18 GMT

@David_Billa wrote:

@Tom @Kurt_Bremser

I need a need a list of macro variables, but a macro variable with a value and b macro variable with b value and so on.

So how to pass these macro variables as arguments to %index function?

The way you phrase this feels to me like you may have had an idea and unwilling to consider other approaches, even if easier or more maintainable.

Any thing with a "list of macro variables" can depend a lot on what the list actually looks like. If a simple space delimited list of single strings then you can pull each individual value from from the list with something like this:

%macro dummy(list = );
%do i = 1 %to %sysfunc(countw(&list.));
   %let parm = %scan(&list.,&i.);
   %put Parm number &i. is &parm.;
%end;
%mend;

%dummy(list= first second other garbage);

So you could stick Parm, or whatever you want to call it in any function, data set or external filename, title text ...

Re: Create macro variables from file names

ballardw — Thu, 10 Dec 2020 18:35:45 GMT

@David_Billa wrote:
I want to do it in a macro as I want to update the existing program which
has macros. I was not allowed to use other than macros.

Another instance of a boss with the "I don't care that a saw is the best tool, use a chisel because I say so!" attitude perhaps?