topic Re: Create new value with macro and loop in SAS Programming

Create new value with macro and loop

km0927 — Sun, 29 Dec 2019 16:12:52 GMT

Hello, I'm new to use do loop with let macro.

I want to create several new variables, with if conditions on pre-existing variables.

Without using macro, the code will be this. A, B, C .. is old variable, _A, _B, _C ... is new variable.

data want;
set have;
by id;
if first.id then _A=A;
if A=1 then _A=A;
else do;
retain _A.;
end;
run;

data want;
set have;
by id;
if first.id then _B=B;
if B=1 then _B=B;
else do;
retain _B.;
end;
run;

... and so on.

With searching, I found it is better to use let function and do loop. However, I'm new to this. I created the code below (only A, B, C), However, it wasn't working.

%Let var = A B C ;
%Let _var = _A _B _C ;

data want;
set have;

do i = 1 to 3;
by id;
if first.id then scan(&_var.,i,' ')=scan(&var.,i,' ');
if scan(&var.,i,' ')=1 then scan(&_var.,i,' ')=scan(&var.,i,' ')
else do; retain scan(&_var.,i,' ');
end;
end;
run;

With log statement, there was problem in scan. It can't read 'A B C'. I hope somebody help me fixing this problem.

Re: Create new value with macro and loop

novinosrin — Sun, 29 Dec 2019 16:30:59 GMT

%Let var = A B C ;
%Let _var = _A _B _C ;

data want;
set have;
by id;
array j &var;
array k &_var;
retain k;
do over j;
 if first.id or j=1 then k=j;
end;
run;

Hi @km0927 What you need is not macro i'm afraid rather an ARRAY. Please observe the grouping of var and _var in two sets of arrays and the loop that traverses through the elements of the arrays. The implicit array DO OVER makes it a very convenient syntax.

Re: Create new value with macro and loop

ed_sas_member — Sun, 29 Dec 2019 16:31:23 GMT

Hi @km0927

You should typically use an array in this case.

An array allow you to perform the same manipulation on a large range of variable.

- First, you declare the array in an ARRAY statement -> ARRAY <name of the array> (*) <$ only if character variables> <your variables>;

- Then you can loop through all the variables of the array by calling them by their name inside the array : for example, variable A is the first variable in the array "new" so you can call it old(1); variable B is equivalent to old(B); ...

data want;
	set have;
	by id;
	
	array old (*) A B C;
	array new (*) _A _B _C;
	
	do i=1 to dim(old);
		if first.id or old(i) = 1 then new(i)=old(i);
		else do;
			retain new;
		end;
	end;
	
	drop i;
run;

Re: Create new value with macro and loop

km0927 — Sun, 29 Dec 2019 16:40:57 GMT

Really thanks. It worked.

However, how did 'k' in array worked?

With 'do over j', j did loop with A, B, C.

But there was no 'do over' with k.

It would be bothering, I would really appreciate your explanation.

Re: Create new value with macro and loop

novinosrin — Sun, 29 Dec 2019 16:53:44 GMT

Sure @km0927 the DO OVER array is an implicit array and hence there is no need to explicitly specify an index variable like do i=1 to n;

Though implicit there is an index variable created in the PDV by the name _I_ , however this variable is not written to the output dataset. So every time the DO OVER loop executes, _I_ is incremented by a value of 1. In essence, what happens, when _I_ is 1 , the array reference is j(_I_) or k(_I_) for the reason both J and K are nonscalar items that points to an array that are collection of variables.

Since J and K array are grouped with same number of items like in your instance 3 elements, it is easy to reference and loop over one array J or K as _I_ increment can correspondingly be referred for elements in K or the other array.

Hope that helps.

PS Recommended reading IMPLICIT SAS DO OVER ARRAYS online