Create macro variables from file names

[David_Billa]

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?

[PeterClemmensen]

Would this include CSV files only?

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

[David_Billa]

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

[David_Billa]

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

[Shmuel]

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/... 

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

[Kurt_Bremser · Accepted solution]

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;

[David_Billa]

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

[Kurt_Bremser]

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.

[David_Billa]

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? 

[Tom]

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.

[David_Billa]

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.'

[Tom]

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;

[Kurt_Bremser]

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? 

---

[David_Billa]

@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?

[Kurt_Bremser]

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.