Iteratively multiplying the values in one row by each row based on a grouping value

Reply
New Contributor
Posts: 2

Iteratively multiplying the values in one row by each row based on a grouping value

Hi all,

 

I'm wondering if you can help me with a coding problem. I'm trying to do the following:

 

I have individual probability values for 24 different age groups. I have 64 unigue sets  (rows) of these 24 probabilities, specific to each of the 64 counties. I have counts for these 24 age groups for 1249 census tracts. I want to apply the unique probabilities for the specific county of interest ONLY to the tracts within that county. In the image below, I'm trying to use the values in row 99 for all of the "Alamosa" County census tracts. Then I'll use the values in Row 104 for all of the "Arapahoe" County census tracts. I'll do this 62 more times for the other counties and their tracts. I know the number of tracts per each county, but that number is not uniform. Meaning I can't say pull every ith row and multiply it for kth number of rows. Do I need something along the lines of:

 

Data want;

   set have;

  IF county = 1 Then NewVar1 = Probability1ofAGEGP1 * CountAGEGP1;

      else if county= 1Then New Var2 = Probability1ofAGEGP2 * CountAGEGP2;

.

.

.

If County = 64 then NewVar 1 = Probability64 of AGEGP1* CountyAGEGP1;

If County = 64 then NewVar 2 = Probability64 of AGEGP2* CountyAGEGP2;   ?

 

Question.png

 

 

Is  there a way to code this in SAS? I was looking at this solution (https://communities.sas.com/t5/SAS-Procedures/Multiply-column-data-to-single-cells-given-grouping-va...) but it only uses one multiplier per row, and I have 24. I'm guessing I also need to specify a "county" value for each of the probability rows.

 

Thanks in advance. 

 

 

 

Super User
Posts: 19,038

Re: Iteratively multiplying the values in one row by each row based on a grouping value

Please post some sample data showing input and output data and what your issue is. 

 

It doesn't need to be real, mock some data up that replicates your issue. PLEASE DO NOT POST images, it means if someone wants to test any code they have to type or mock up your data themselves. 

 

 

Valued Guide
Posts: 947

Re: Iteratively multiplying the values in one row by each row based on a grouping value

This is solved simply by maintaining a 64*24 matrix of probabilities, where each row is for one of the 64 counties (variable county =1 to 64) and each column is a probability for the corresponding age group.

 

Assume you have a 64 observation data set name county_probs with 25 variables: county, ageprob1 ageprob2, ... ageprob24.

 

And a second data set have called TRACTS with the vars in your picture (id1 id2 geography county age_18_over agegp_1...agegp_24)

 

The code below is untested:

 

Notes:

  1. The designated _TEMPORARY_ is not reset to missing with each iteration of the DATA step, and while its contents can be used in the data step, they are not written to the output data set.

 

data want;
  /* Read in your set of 24 probabilities for each of 64 counties*/
  /* Put it into a 64*24 matrix named probs_by_county */
  array probs_by_county {64,24} _temporary_;

  array ageprob{24}  ageprob1-ageprob24; /* in the COUNTY_PROBS dataset*/
    
  /* First fill the matrix */
  if _n_=1 then do until (end_of_cp);  
    set county_probs end=end_of_cp;
	do grp=1 to 24; 
      probs_by_county{county,grp}=ageprob{grp};
	end;
  end;
  drop ageprob1-ageprob24;

  /* Now read the tract file and use the matrix above */
  array agegp_ {24}  agegp_1-agegp_24; /*In the TRACTS dataset*/
  array newvar{24}   newvar1-newvar24;

  set tracts;
  do grp=1 to 24;
    newvar{grp}=agegp_{grp}*probs_by_county{county,grp};
  end;
  drop grp;
run;

 

Regards,

Mark

Super User
Posts: 9,856

Re: Iteratively multiplying the values in one row by each row based on a grouping value

It is best for DOW + hold skill.


data have;
 set sashelp.class;
 keep sex age;
run;
proc sort data=have;by sex;run;
data temp;
array x{99999} _temporary_;
n=0;
 do until(last.sex);
  set have;
  by sex;
  n+1;
  x{n}=age;
 end;

 do i=2 to n;
  value=x{1}*x{i};output;
 end;
 keep sex value;
run;
proc transpose data=temp out=want;
 by sex;
 var value;
run;

Ask a Question
Discussion stats
  • 3 replies
  • 217 views
  • 0 likes
  • 4 in conversation