BookmarkSubscribeRSS Feed
BenWW85
Fluorite | Level 6

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. 

 

 

 

3 REPLIES 3
Reeza
Super User

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. 

 

 

mkeintz
PROC Star

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

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Ksharp
Super User
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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1686 views
  • 0 likes
  • 4 in conversation