Use Do Loop to Create Fields

[theponcer]

I have a data set of rabbit counts by region over several years. The only problem is, the data set has two sets of fields for each region, and only one of those fields is populated. I am trying to collapse the fields so that I only have one field for rabbit count. I am using the coalesce function for this. For example, if Region_1 count = 5, then the corresponding Tract_1 count will be blank. If Tract_2 count=1,733, then the corresponding Region_2 count will be blank. However, SAS does not seem to accept using the counter variable as part of the field name. Any ideas? 

data rabbit_reformat;
set rabbits ;
do i=1 to 144;
Count_&i. =coalesce(Region_&i.,Tract_&i.);
end;
run;

I am trying to reformat the table, NOT sum it up at this point. 

[Reeza]

You need to explainhow your data is structured and what you expect as output. Without that, no way to know if your code is correct. and you cannot use &i in the manner you are. You can use arrays if needed, but I suspect you don't.

[theponcer]

This is what I have:

State	Region_1	Tract_1	Region_2	Tract_2
AZ	Five			One Thousand Seven Hundred and Thirty Three
CA		Eight	Two Thousand and Three

and this is what I want:

State	Rabbits_1	Rabbits_2
AZ	Five	One Thousand Seven Hundred and Thirty Three
CA	Eight	Two Thousand and Three

[novinosrin]

data have;
input State $	Region_1	Tract_1	Region_2	Tract_2;
cards;
AZ	5		.	1733 . .
CA	.	8	2158 . .
;	

data temp;
set have;
array t Region_1--Tract_2;
do i=1 to dim(t);
if t(i)>. then do;
temp=t(i);
output;
end;
end;
run;

proc transpose data=temp out=want(drop=_:) prefix=Rabbit_;
by state;
var temp;
run;

[Reeza]

No loops or anything else needed then, SAS loops each automatically.

rabbits_1 = coalesce(region_1, tract_1);
rabbits_2 = coalesce(region_2, tract_2);

If this needed to be generalized beyond two cases then an array would be the solution.

[Astounding · Accepted solution]

Given that you have 144 sets of variables:

data want;
   set have;
   array count_ {144};
   array region_ {144};
   array tract_ {144};
   do i=1 to 144;
      count_{i} = coalesce(region_{i}, tract_{i});
   end;
   drop i;
run;

[Reeza]

@Astounding I think the OP has 144 observations, not 144 variables....though it's possible they have a data set with 288+ columns.

---

@Astounding wrote:

Given that you have 144 sets of variables:

data want;
   set have;
   array count_ {144};
   array region_ {144};
   array tract_ {144};
   do i=1 to 144;
      count_{i} = coalesce(region_{i}, tract_{i});
   end;
   drop i;
run;

---

[Kurt_Bremser]

Transpose, and you don't need arrays or dynamic code.