Looping macro issue

[Pandu2]

Hi All, I created a looping macro and passing date variable as parameter to loop the process but the isn't taking those date values as-is instead. For instance,

06JUN2022 - Supposed to take but taking this value 22802. I need my macro loop to take the date values as-is.

%macro loop(date1=);
Code to work repeatedly
%mend loop;

Data _null_;
Set work.have;   * this input table has date values for date1 variable;
If _n_>=1 then call execute(%nrstr(%loop(
date1=' || strip(date1) ||'))');
run;

This is my loop code; 
Please help me on this, Thanks!.

[PaigeMiller]

SAS date values are the number of days since 01JAN1960, so 22802 is fine.

The comparison DATE1=22802 ought to work properly, testing to see if DATE1 is equal to 06JUN2022, if DATE1 is numeric (and if it is not numeric, it should be). No need to format macro variables to be human readable (such as 06JUN2022) for arithmetic or logical operations.

I think you have other syntax issues, such as having a loop in side of CALL EXECUTE, I have no idea what that is supposed to do, but I don't think it will work.

Please explain further what this code is trying to do, as it seems to me there are simpler ways of doing whatever you are trying to do. Please do not ignore this request.

[Pandu2]

Thanks for replying me, the code which I posted was working for my previous projects but when comes to this project the date1 value should as-is cuz under the macro loop code I've a bunch of tables which takes that date1 variable value as input cuz those tables has date embedded at the end. 

[Patrick]

You don't show us enough to really know but... first issue: You have unbalanced quotation marks.

Based on your code variable date1 in data set have is a character variable. I assume it's a string like 01jan2022?

Does below work for you?

data _null_;
  set work.have;   * this input table has date values for date1 variable;
  if _n_>=1 then call execute( cats('%loop(date1=',date1,')') );
run;

If date1 in your have dataset it a numerical variable with a SAS date value then you might need code similar to below:

data _null_;
  set work.have;   * this input table has date values for date1 variable;
  if _n_>=1 then call execute( cats('%loop(date1=',put(date1,date9.),')') );
run;

[Quentin]

Hi,

If your macro expects the text value 06JUN2022 you can use PUT to convert your numeric date to that value:

%macro loop(date1=);
%put >>&=date1<< ;
%mend loop;

Data _null_;
date1="06Jun2022"d ;
If _n_>=1 then call execute('%nrstr(%loop(date1=' || strip(date1) ||'))');
If _n_>=1 then call execute('%nrstr(%loop(date1=' || put(date1,date9.) ||'))');
run;

Without the PUT, SAS is doing an implicit numeric to character conversion for you, and converting the number 22802 to '22802'.  That creates the NOTE in your log, "NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).".  Many people think that NOTE should be considered an error.

[Pandu2]

Thanks for the code. My date1 variable in that table is of date type.  And the code inside the macro loop has some tables which has dates embedded in it and some columns as well. Lemme show you my code.

Data want;
Set xyz.abc_06jun2022;
array values id count price_05jun2022;
Pd= catx(" - ", vname(values(whichn(price_05jun2022, of values (*)))), price_05jun2022);
run;

Here the column name for price_05jun2022 keeps on changes on daily basis. For instance for today's date the column name will be price_110ct2022.

This is the code which should loop in that loop macro. When comes to your code it's working fine for the input table name of the above code but not working for the column name i.e price_date2.

[Pandu2]

Please look at the newly added reply for Tom.  

[Tom]

You are missing a quote.

You can use CATS() function to simplify the data step code.

You can limit the %NRSTR() macro quoting to just the macro name.

data _null_;
  set work.have; 
  call execute(cats('%nrstr(%loop)(date1=',date1,')'));
run;

If you need to values of DATE1 in some particular style then you can use PUT() to apply some date format to the values in DATE1.  For example you mentioned that you where appending the date value to then end of filenames. In that case you would want to use a format like YYMMDDN8. so that the names will sort in chronological order.

  call execute(cats('%nrstr(%loop)(date1=',put(date1,yymmddn8.),')'));

[Pandu2]

I've added another parameter in this macro loop for the below code.

%macro loop(date1=, date2=);

Data want;
Set xyz.abc_06jun2022; ( date1 parameter goes to table name ) 
array values id count price_05jun2022; ( date2 parameter goes to column name)
Pd= catx(" - ", vname(values(whichn(price_05jun2022, of values (*)))), price_05jun2022);
run;

%mend loop;

Data _null_;

Set work.have; * this input table has date values for date1 variable;

If _n_>=1 then call execute(%nrstr(%loop(

date1=' || strip(date1) ||'))');

run;

[Tom · Accepted solution]

Change the macro to actually use the parameter value to generate the code.

Use the DATE9. format to convert the values of the variable DATE1 into the style you need to construct the variable name.

%macro loop(date1=);
%local date2;
%let date2=%sysfunc(putn("&date1"d-1,date9.));
data want;
  set xyz.abc_&date1; 
  pd = catx(" - ","price_&date2",of id count price_&date2);
run;
%mend loop;

data _null_;
  set work.have; 
  call execute('%nrstr(%loop)(date1=',put(date1,date9.),')'));
run;

Later you can work on changing how you create those datasets so that the DATE is not part of either the name of the dataset or the name of the variable.  You should keep the date values in a variable where they are easier to work with.

[Pandu2]

Thanks for the code. The date1 & date2 variables has numerous records n the date2 value shouldn't be constant.

[Tom]

---

@Pandu2 wrote:

Thanks for the code. The date1 & date2 variables has numerous records n the date2 value shouldn't be constant.

---

That is as clear as mud.

Assuming that you mean the dataset you are using to generate the macro calls has two variables you want to use to call the macro the adaptation of the code to generate the string to pass to CALL EXECUTE is trivial.  

Similarly the macro will need to be modified to accept both DATE1 and DATE2 as inputs instead of deriving DATE2 from DATE1.

Try it and see how it works.  If it does not work then post the code you tried and the SAS log with any errors that are generated. 

As always get a working version of the code you want the macro to generate before trying to convert the code into a macro.   For example the code you posted using the WHICHN() function made no sense.  You searched a series of variables for one with the value in the PRICE_&DATE2 variable,  but the only variable in the list that would have had values like that was the PRICE_&DATE2 variable itself. The others where ID values.

[Pandu2]

Yeah, your assumption is so right.

[Pandu2]

Thanks Tom, your code is working for me. 

[Kurt_Bremser]

This is useless:

If _n_>=1 then

If there is at least one observation in the dataset, this condition will always be true; if there are no observations in the dataset, this statement will never be reached.

You miss a single quote here before the %NRSTR:

call execute(%nrstr(%loop(date1=' || strip(date1) ||'))');

so the macro processor will resolve

%nrstr(%loop(date1=' || strip(date1) ||'))

once before the data step even runs, resulting in syntax errors.

The code should be

call execute('%nrstr(%loop(date1=' || strip(date1) ||'))');

Finally, it is always easier to use raw values as parameters, so your macro should accept the number of days, as you then do not need to wrap it in quotes followed by a d wherever you use it. See Maxim 28.