looping over the information included in a column

[Sonyboy]

Dear all:

I want to create a little macro(example loop)   that loops over each of the dates in the first column of the below  table; I would like to understand how I can link the information in the date column in the excel file in order to create a macro that creates sub-samples based on the dates. For example,a code like this, where there is macro within a macro etc..

My question is how to use the date information in the table (pls see column in the attached excel file) so that the macro loops over each row in the column one-by-one.This is important as the information I am processing has huge number of rows

Regards,

The macro in which I want the use information in Column 1:

%macro  example loop(date,y);

   Data a_&y ;

    Set b ;

    Where date=&date ;

     Run;

     Proc reg data=a_&y;

      Model whatever

    Out=result_&y

 %anothermacro(result_&y)

……

%mend exampleloop:

[LinusH]

Assuming that your tables data is in a SAS data set.
Use a data step and use CALL EXECUTE.
To optimize your macro, I'm pretty sure you could use WHERE with proc reg, so you can avoid the extra subsetting step.

[Sonyboy]

please see my comment to Reeza. I have a list emanating from a completely distinct dataset. The dataset I use in the macro are totally different from the one I show in the excel file. The only common point is that the list of dates. I want to use this excel list in another macro as an input to a looping process. As a matter of fact, for each date in the excel, I want the create a subset data through "where" and run the analysis...Can you please give me a code example of what you have in mind?

[Reeza]

Also, why not use BY processing?

proc sort data=sashelp.stocks out=stocks;
by stock date;
run;

proc reg data=stocks;
by stock;
model close = open;
run;

[Sonyboy]

Dear Reeza,

thank you. However, in this case, the data set shown in the excel file and the data set "a" that I use in the prog reg are two separate and independent data sets. The only information that I would like to use from the excel file is the list of dates...otherwise the data to be used in the reg has no similarity with the data that I use within the macro in the proc reg.

the idea is to bring in each of these dates in the macro ...is this now clearer?

[RW9]

I would agree with @Reeza here.  Create a dataset of your dates for this Excel file.  Then do one step:

proc sql;
  create table WANT as
  select *
  from   HAVE
  where DATE in (select date from DATES_DATASET);
quit;

This then creates a dataset called want, which only has the dates in the list from Excel.  Then run whatever procedure you want there after with a by group on the date variable.  By group processing is far quicker than running the procedure for each set, and is far easier to read than messy unecessary macro code.

[Reeza]

Create a data that contains the entire list and then use BY.

If you really want a macro, then call execute is what your looking for. The documentation has a good example of the usage.

[Sonyboy]

Dear Reeza et all,

I am trying to get the following macro work. However, the system does not recognize this...

'am' is simply a list of numbers....

the macro %soner(x)  works  when ones run it in the format of %soner(1) etc

however, the below macro does not function properly neither with || or without it...When I do not put || them it simply takes on the the character variable am...

How can I fix this?

data _null_;
set index;
call execute("%soner('||am||')");
run;

[Reeza]

I don't think you're passing the correct string to call execute.

Try creating the string outside of the call execute, make sure it looks correct and then either move that code to your call execute or pass it the variable name.

Also, don't use double quotes when building your string for call execute. It will try and resolve the macro, which you don't want it to do and will generate warnings in your log.

[RW9 · Accepted solution]

Hi,

First, can I ask why the solution I provided does not work for you.  It is far better to use Base SAS programming and by groups for both efficiency and ease of programming.  Macro language is not designed as a replacement for this.

Secondly, you make no examplpe in your post of what this "index" dataset looks like or contains.  For a full answer always provide example test data in the form of a datastep so we can see what your working with.  I will guess that it looks like this:

Index:

Var=Am=Numeric

1

2

3

Now, you can consider the datastep to be a loop, each iteration happens over one observation at a time. Call execute function accepts a valid string, i.e. characters enclosed in quotes, and this gets pushed out to the compiler after the datastep finishes execution.  That text then of course, has to be valid SAS code.  So what will this line generate:

call execute("%soner('||am||')");

if numeric 1 is placed in there - remember there is padding in a variable:

%soner('     1')

something like that.  So I would suggest something like:

data index;
 am=1; output;
 am=2; output;
run;

%macro soner(a);
%mend soner;

data _null_;
 set index;
 call execute(cats('%soner(',put(am,1.),');'));
run;

What this should do is create two extra rows in the code which call the macro twice.  However, all of this can be avoided by simply using by groups.

[Sonyboy]

thank you so much. This worked brilliantly.

[Sonyboy]

Hello there,

with the call execute step, if one step is taking too much time and blocking all the steps, how can I tell the system to move on after a while..How can I integrate this into the below piece fo the codeIs there a timeout option? 

data _null_;
 set index;
 call execute(cats('%soner(',put(am,1.),');'));
run;

 thanks for your response

[Sonyboy]

Just some further information for the above question.

for example in the above code,let'us assume the two consecutive values are 2; 103 in the index.let's assume that for the value=2, the %macro just turns in an infinite manner...I want to tell the system that if there are no results after some time, I want it to move the next value=103...

how can I achieve this?

thank you

[Reeza]

Your original question is answered. Please post new questions in a new thread. 

[RW9]

Hi,

I will respond here, but as @Reeza has stated this is a new topic.  Let me first explain what Base SAS and Macro SAS are:

Base SAS is compiled code which operates on data to process and manipulate the data.

Macro SAS is a code generation tool, to help repeat typing of code.

The call execute has no impact whatsoever on operation time of the Base SAS code you have programmed or generated from code.  Nor will it have any control over it.  The reason being, well the code that feeds into the compiler - that which actually does something to the data, is already fully expanded by the pre-processor (the macro compiler if you will), and none of that remains, so cannot know how long a step takes to work.

Now we know what the two let me ask you the question I asked a couple of times before in this post, and that is why are you not using Base SAS programming to do you processing, what "need" is there for code generation and macro processing?  If you write your Base SAS code efficiently - i.e. using by groups and such like - this reduces the number of load/close for each of the files.  It will make your code far more readable (and if you have ever had to pick up someones else code you will know what I am talking about), far more efficient, and far more maintainable.  In 99% of the time cases Base SAS can do what you want, though it may require sideways thinking e.g. restructuring data.  

Onto the question, this is down to what is happening in your macro, you haven't posted it, so I have no idea.  You can't conditionally stop a datastep running based on resource use (at least easily), it is one step which runs, then the next one etc.  Optimise your code - see the paragraph above here - write efficient Base SAS code.  Once that is running efficiently, then look at seeing if you can reduce the code somewhat with Macro.  Don't just write macro programs to cover up the lack of Base SAS programming.

Without further example I can't say much else.