concatenate macros in a loop

[CP2]

Does anyone know how I can concatenate macro variables. Here is what I am working with:

It is running through an entire database and if Total is less than a certain level (levels are related to a variable names in &lst) then I want to concatenate the variable names and use later. Total is a different value for all values of Type. The actual database is irrelevant; all I need are the concatenated macro variables to use in a group by statement later on. 

%let lst = var1 var2 var3 ;
%let var_count = 3 ;

DATA want;
do until (last.type) ;
SET have ;
by type ;
ARRAY level[3] (5 4 3) ;    /*a macro variable will be placed here so these values will change in my bigger program*/ 
%let k=1 ;
do k=1 to &var_count. ;
%LET current=%scan(&lst.,&k.);
IF total < (level[k] * 2) 
THEN DO;
%LET low_lst = &current.||&&&low_lst. ;
END ;
ELSE %Let Oth_lst= &current.||&&&oth_lst. ;
%put &oth_lst. ;

end ;
end ;
output ;
run;

[Tom]

The macro processor will interpret your macro code before the SAS compiler tries to interpret the generated statements.

So you essentially submitted this program (but your program is missing a semi-colon).

%let lst = var1 var2 var3 ;
%let var_count = 3 ;
%let k=1 ;
%LET current=%scan(&lst.,&k.);
%LET low_lst = &current.||&&&low_lst. ;
%Let Oth_lst= &current.||&&&oth_lst. ;
%put &oth_lst. ;

DATA want;
do until (last.type) ;
  SET have ;
  by type ;
  ARRAY level[3] (5 4 3) ;  
  do k=1 to &var_count. ;
    IF total < (level[k] * 2) THEN DO;
    END ;
    ELSE ;
  end ;
end ;
output ;
run;

[CP2]

I wasn't thinking of that for global token variables but that makes sense. Any idea how I can assign the variable names to a macro variable (for later use) if the data in their column doesn't/does meet a certain condition?

[Tom]

You need to explain what you want to do in more detail to get an real answer.  At this point it does not really make any sense to me.

[CP2]

data have;
input type $ total var1 $ var2 $ var3 $ ;
datalines ;

small 30 a 1 x
small 30 b 1 x
large 7 a 2 x
large 7 b 3 y
large 7 c 4 y
;
run;

There is a value associated with each column based on a macro variable. In this case, 5 for var1, 4 for var2, and 3 for var3. I don't really care at this point what content is in the var1 to var3 columns. I just want the names of the variables in a string based on the condition that looks at variable 'total'. The 5,4,3 values come from a different macro variable that are associated with var1, var2 and var3. 

pseudo code:

variable list, lst, should equal the string, 'var1 var2 var3' 
do k = 1 to 3 (loop through the number of variables)

'current' should equal kth word in the variable list: %scan(&lst., &k.)
if total < 5 * 2 then want =catx(',', want, current) else other=catx(',', other, current)
if total  < 4 * 2 then want =catx(',', want, current) else other=catx(',', other, current)
if total < 3 * 2 then want =catx(',', want, current) else other=catx(',', other, current)
In the end for this example and for blue type want should equal 'var1,var2,var3' and other should be blank because total =30 for small type

also, loop through the values of type variable. 
if total < 5 * 2 then want =catx(',', want, current) else other=catx(',', other, current)
if total < 4 * 2 then want =catx(',', want, current) else other=catx(',', other, current)
if total < 3 * 2 then want =catx(',', want,  current) else other=catx(',', other, current)
In the end for this example and for red type 'want' should equal 'var1,var2' and other should equal 'var3' because total=7 for large type

[Tom]

So it looks like your decision is based just on data like:

data totals;
input type $ total ;
datalines ;
small 30 
large 7 
;

How did you pick 30 and 7 as the values to use for 'small' and 'large'?  Is that the MAX() the MIN()? or is TOTAL a constant for particular value of TYPE?

So let's assume that you have the list of cutoffs and names in macro varaibles. Also that you have a macro variable with the list of cutoff values.

%let nobs=3; 
%let cutoffs=5 4 3 ;
%let names=var1 var2 var3 ;

Then to generate the list of values from the data above is simple.

data want ;
  set totals ;
  length want other $200 ;

  array cutoffs (&nobs) _temporary_ (&cutoffs);
  array names (&nobs) $32 _temporary_ ("%sysfunc(tranwrd(&names,%str( )," "))");

  do i=1 to &nobs;
    if total < 2*cutoffs(i) then want=catx(' ',want,names(i));
    else other=catx(' ',other,names(i));
  end;
  keep type total want other ;
run;

Obs    type     total      want       other

 1     large       7     var1 var2    var3
 2     small      30                  var1 var2 var3

Not sure how you want to put these four strings into macro variables.

[CP2]

Thanks. I went down that road already but I need to still transfer the content in the columns into macro variables because I then want to use those variables in the 'group by' statement of a proc sql to match to the original content on those var1-var3 variables to another database to create new variables from an aggregate. And, probably need to do the match in a loop for each 'type' value.  It's a database weighting task but I think it's too complicated to explain so I'll just keep trying things out. I'll figure it out eventually. Thanks for your help though. 

[Tom · Accepted solution]

---

@CP2 wrote:

Thanks. I went down that road already but I need to still transfer the content in the columns into macro variables because I then want to use those variables in the 'group by' statement of a proc sql to match to the original content on those var1-var3 variables to another database to create new variables from an aggregate. And, probably need to do the match in a loop for each 'type' value.  It's a database weighting task but I think it's too complicated to explain so I'll just keep trying things out. I'll figure it out eventually. Thanks for your help though. 

---

You could probably wrap the loop around everything and add a WHERE TYPE="&type" statement where appropriate to get to a single set of macro variables.  Or you could wait and do the looping later.

proc sql noprint ;
select type,want,other
  into :type1- 
     , :want1- 
     , :other1- 
  from want
;
quit;

%do i=1 %to &sqlobs;
  %let type = &&type&i;
  %let want = &&want&i;
  %let other = &&other&i;
   ... do stuff with the macro variables TYPE, WANT and OTHER ...
%end;

[CP2]

Actually, i've never seen the way you added the sysfunc to the array. Could you explain the logic of adding this 

("%sysfunc(tranwrd(&names,%str( )," "))");

I know what the tranwrd function is doing but I don't know how it is used with the array. 

[Tom]

---

@CP2 wrote:

Actually, i've never seen the way you added the sysfunc to the array. Could you explain the logic of adding this 

("%sysfunc(tranwrd(&names,%str( )," "))");

I know what the tranwrd function is doing but I don't know how it is used with the array. 

---

You can provide a set of initial values for an ARRAY. When the array is characater then the values need to be string literals like "A" "B" "C" etc.  If you take a space delimited list of words and change the spaces between the words to spaces with quotes around them and then add quotes to the beginning and the end then you get a list of quoted string literals.

[ballardw]

You should provide some example data small enough that you can show the behavior before and after that this is supposed to provide.

From the mixture of datastep and macro statements you may have a misunderstanding of how macro variables work.

Since %let is not part of datastep code then it is not conditional on any values encountered or created during the data step.

If you need to create a macro variable then the statement would be Call Symputx or Call symput, not %let.

The syntax would be

Call symputx('nameofmacrovariable', textvalue);

With a %let statement you almost certainly do not want to use the || operator. Please examine this code:

%let a= first;
%let b= second;
%let ab = &a||&b;
%put &ab;

In my brief exampt above if I want to generate a variable with the value of first second then

%let ab = &a &b;

the space is signficant.

[CP2]

I also tried this in the variable assignment:

%let low_lst = %sysfunc(catx(',',&current.,&&&low_lst.)) ;

[Tom]

What are you trying to do?  To concatenate the values of two macro variables just expand them next to each other.

%let a=cow;
%let b=boy;
%let c=&a.&b;

[CP2]

but I need to do it in a loop so would need to combine the prior value of c in your example. 

[Tom]

---

@CP2 wrote:

but I need to do it in a loop so would need to combine the prior value of c in your example. 

---

Say you want to generate VAR1 VAR2 .... VAR&n into a single macro variable.  Inside of a macro you could use %DO loop.

%let varlist=;
%do i=1 %to &n ;
  %let varlist=&varlist VAR&i;
%end;

 Or you could use a data step.

data _null_;
  length varlist $32767;
  do i=1 to &n ;
    varlist=catx(' ',varlist,cats('VAR',i));
  end;
  call symputx('varlist',varlist);
run;