Reference a macro in another macro variable's name

[loredana]

Hello, this might be silly, but I'm having trouble creating macros interactively in a do loop;

So, what I'm trying to do is create a macro variable per customer ID that would store all negative values for the variable Points for that specific client.

My code is like this:

%macro redlist();

%do i=1 %to &N_Clients.;
	
	proc sql noprint;
		select Points_Redeemed into: red_values_client_&i. separated by " "
		from test
		where ID=&i. and Points<0
		order by ID;
	quit;
%end;

%mend;

%redlist()

Now, someone said and remember reading something along those lines that I cannot reference a macro (&i) in a select statement in proc sql. My alternative approach, that also doesn't work, is this:

%macro redlist();

%do i=1 %to &N_Clients.;

	proc sql noprint;
		select Points_Redeemed into: red_values_client separated by " "
		from test
		having ID=&i. and Points<0
		order by ID;
	quit;

	%let red_values_client_&i.=&red_values_client;

%end;

%mend;

%redlist()

Any help? Anyone?

[gamotte]

You should declare the macrovariables outside the macro (or inside with a global satement). Otherwise you will lose them at the end of the macro execution.

[loredana]

Could you expand that please?

I've modified th3 2nd code with this statement:

%global red_values_client_&i=&red_values_client;

The problems are: the &i reference is not resolved, which makes my macro name invalid;

I cannot declare this macro outside of the do loop since I need a macro created for each client

[Kurt_Bremser · Accepted solution]

What @gamotte said, here with an example:

data have;
input id points;
cards;
1 3
1 -2
1 4
1 -1
2 3
2 0
2 -1
3 -5
3 4
;
run;

%macro test_for_i;
%do i = 1 %to 3;
%global test_&i;
proc sql noprint;
select points into :test_&i separated by " " from have where id=&i and points < 0;
quit;

%end;
%mend;
%test_for_i;

%put test1=&test_1 test2=&test_2 test3=&test_3;

And this is the log:

16         data have;
17         input id points;
18         cards;

NOTE: The data set WORK.HAVE has 9 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.02 seconds
      cpu time            0.01 seconds
      
28         ;

29         run;
30         
31         
32         
33         %macro test_for_i;
34         %do i = 1 %to 3;
35         %global test_&i;
36         proc sql noprint;
37         select points into :test_&i separated by " " from have where id=&i and points < 0;
38         quit;
39         
40         %end;
41         %mend;
42         %test_for_i;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
2                                                          The SAS System                              09:09 Thursday, July 21, 2016

      

43         
44         %put test1=&test_1 test2=&test_2 test3=&test_3;
test1=-2 -1 test2=-1 test3=-5

[RW9]

Post example data in a datastep, and what the output should look like/is used for.  Creating X amoutn of text macro variables containing lists of numeric data I can guarantee you is not the best way to do things.  If you want a dataset with all negative values in one row, then simply:

proc sort data=have;
   by id;
   where points < 0;
run;
proc transpose data=have out=inter;  
  by id;
  var points;
run;
data want;
  set inter;
  result=catx(' ',of col:);
run;

[loredana]

I know what you mean, but it's the only idea I've had that makes sense at this point. My overall problem is this:

I have a Points variable with positive and negative values. Each time a negative value pops up I need to deduct it from previous positive values. At each point I would build a balance which says how many remaining points there are for that row. Once the balance is positive, those points would be used as the starting point when deducting the next negative points. An example would be:

[RW9]

Could you post some example in data of your original data (in a datastep), as I would think this can be done by retain statements, but its hard to tell what is going in and what the output should look like, example of retain;

data have;
  informat date date9.;
  input id date points;
  format date date9.;
datalines;
1 01jan2016 100
1 02jan2016 110
1 03jan2016 120
1 04jan2016 130
1 04jan2016 -360
;
run;

data want;
  set have;
  retain positive_points negative_points balance;
  by id;
  if first.id then do;
    positive_points=0;
    negative_points=0;
    balance=0;
  end;
  if points > 0 then positive_points=sum(positive_points,points);
  else negative_points=sum(negative_points,points);
  balance=positive_points+negative_points;
run;

[loredana]

This is a piece of the dataset:

data have;
	input ID Date:ddmmyy9. Positive_Points Negative_Points;
	format Date ddmmyy9.;
	cards;
1 23/10/2015 80.47 0
1 24/10/2015 25 0
1 01/11/2015 135.87 0
1 02/11/2015 511.53 0
1 03/12/2015 270.41 -1000
1 12/12/2015 168.75 0
1 14/12/2015 299.2 0
1 18/12/2015 323.51 0
1 28/12/2015 502.14 -800
1 07/01/2016 403.2 0
1 09/01/2016 224.22 -900
1 14/01/2016 128.72 0
1 18/01/2016 721.25 -500
1 20/01/2016 1280.89 -24
2 26/10/2015 25 0
2 26/11/2015 33.03 0
2 03/12/2015 19.98 0
2 08/12/2015 33.71 0
2 29/12/2015 8.47 -100
2 07/01/2016 211.43 0
2 22/01/2016 7.99 -200
2 23/01/2016 15 0
2 26/01/2016 17.98 0
2 30/01/2016 62.99 0
2 14/02/2016 57.97 -100
2 16/02/2016 218.64 0
2 17/02/2016 4.99 0
2 18/02/2016 74.75 -312.4
2 09/03/2016 57.97 -100
2 19/03/2016 94.87 0
2 07/04/2016 87.4 0
2 08/04/2016 94.47 0
2 09/04/2016 35.42 -100.2
2 10/04/2016 125.97 0
2 11/04/2016 0.77 -200
2 12/04/2016 10.77 0
2 21/04/2016 13.49 0
2 22/04/2016 26.98 0
2 23/04/2016 44.99 0
2 26/04/2016 16.9 -100
2 01/05/2016 196.96 -200
2 12/05/2016 10.77 0
;run;

The idea is that if some points were partially or not at all used, they report to the next period, but at the same time I need to know how many of them were used to deduct the first negative points and how many were used for the 2nd, 3rd, etc... 

Also, I need to start deducting from the oldest points.

In this case, Balance for the 1st customer should look like this: 

1st row Balance=80.47-1000=-919.53

2nd row Balance=25+(-919.53)

...

5th row Balance=23.28

insert new row because what's left is used for the next negative points

Positive_Points=23.28                   Balance=23.28-800;