How to create a global variable loop

[shlomiohana]

Hello,

I have a table called sample that contains 5 records.

data sample;
  infile datalines;
  input dates :$10.;
  datalines;
01MAR2022
02MAR2022
03MAR2022
10MAR2022
13MAR2022
;

I want to run 5 times the 2 queries, each run with a different value of the dates field.

First time:

data test;
set dwh;
where a= '01MAR2022';
run;

data test2;
set dwh2;
where a2= '01MAR2022';
run;

A second time:

data test;
set dwh;
where a= '02MAR2022';
run;

data test2;
set dwh2;
where a2= '02MAR2022';
run;

The loop will run as the number of records in the sample table.

I would appreciate your assistance.

[ballardw]

Why bother? The approach you show will only have the values from the last one if you "loop over" over those values and assign the records to only Test and Test2 data sets.

You are missing a step somewhere.

If you look up the CALL Execute statement you can use that to create code that executes for values in a data set.

Here is an example that will "loop over" the values for the first data set. The second would be just as simple, make a second call to call execute with the different set and data names.

data sample;
  infile datalines;
  input dates :$9.;
  call execute ("data test; set dhw; where a='"||dates||"';run;");
  datalines;
01MAR2022
02MAR2022
03MAR2022
10MAR2022
13MAR2022
;

Are you 100 percent certain that all of these "date" appearing values are actually character?

[shlomiohana]

This is just an example,  I have a code of 200 rows that I need to run as the number of records in the sample table, and for each run, I need to use the record of the dates field, it should be a global variable that changes each run.

[Tom]

What you appear to be describing is a macro that takes one input parameter.

So first use a macro variable reference everywhere in the 200 lines that you need to reference that date value. Say you decide to use DATE as the name of your macro variable then your code might look something like:

data test;
  set dwh;
  where a= &date ;
run;

Then add a %LET at the top and set DATE to one of those values from your dataset.  Make sure to take into account that macro variables are just text strings.  So be careful about what values you put into date and how you use the reference to it to generate code.  For example if the variable A is character you might do one of these combinations of type of text in DATE and how the code is generated using &DATE.

%let date=01MAR2022;
where a="&date";
...
%let date="01MAR2022";
where a=&date;
...
%let date='01MAR2022';
where a=&date;
...

But if A is a numeric variable with actual SAS date values then you might do one of these combinations instead. 

%let date=01MAR2022;
where a="&date"d;
...
%let date="01MAR2022"d;
where a=&date;
...
%let date='01MAR2022'd;
where a=&date;
...
%let date=22705;
where a=&date;
...

Once that works now convert the 201 line program into a macro definition.  Remove the %LET at the top and add a %MACRO statement.  Add a %MEND statement to the end.  Say you decided to call the macro MYMACRO then your new 202 line program will look like:

%macro mymacro(date);
.... your original 200 lines ...
%mend;

And to use it you would just call the macro with the value you want it to use.

%mymacro(date=01MAR2022)

Now you can use your dataset with a list of values to call the macro multiple times.

data _null_:
  set sample;
  call execute(cats('%nrstr(%mymacro)(date=',dates,')'));
run;

[Reeza]

You will want to wrap the code you want to execute in a macro and then use CALL EXECUTE to call that macro from the data set. 

Start with a working program first and then you can do the conversion. 

See the tutorials below to get started:

UCLA introductory tutorial on macro variables and macros
https://stats.idre.ucla.edu/sas/seminars/sas-macros-introduction/

Tutorial on converting a working program to a macro

This method is pretty robust and helps prevent errors and makes it much easier to debug your code. Obviously biased, because I wrote it 🙂 https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

Examples of common macro usage
https://communities.sas.com/t5/SAS-Communities-Library/SAS-9-4-Macro-Language-Reference-Has-a-New-Ap...