topic Re: Use Do Loop to Create Fields in SAS Programming

Use Do Loop to Create Fields

theponcer — Wed, 27 Mar 2019 20:31:04 GMT

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.

Re: Use Do Loop to Create Fields

Reeza — Wed, 27 Mar 2019 20:37:00 GMT

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.

Re: Use Do Loop to Create Fields

theponcer — Wed, 27 Mar 2019 20:54:27 GMT

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

Re: Use Do Loop to Create Fields

novinosrin — Wed, 27 Mar 2019 21:00:23 GMT

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;

Re: Use Do Loop to Create Fields

Reeza — Wed, 27 Mar 2019 21:02:05 GMT

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.

Re: Use Do Loop to Create Fields

Astounding — Wed, 27 Mar 2019 21:17:22 GMT

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;

Re: Use Do Loop to Create Fields

Kurt_Bremser — Wed, 27 Mar 2019 21:30:36 GMT

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

Re: Use Do Loop to Create Fields

Reeza — Wed, 27 Mar 2019 21:37:12 GMT

@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;