How to create a variable corresponding to the observations of a level of another variable

[Banke]

Hello everyone,

I have a dataset like in the table below. Please, how do I create pre_cost and post_cost variables such that their values are the sum of costs corresponding to the pre-era (1) and post-era (2) respectively? Thank you so much.

id	pre_post_period	cost	pre_cost	post_cost
001	1	50	150	50
001	1	100	150	50
001	2	50	150	50
002	1	150	350	75
002	1	200	350	75
002	2	30	350	75
002	2	45	350	75

[mkeintz]

Since the data are sorted by ID, you can read each ID twice.  In the first pass, accumulate total pre-costs and total post_costs.  In the second pass, retrieve those totals and output the results.

In the absence of sample data in the form of a working DATA step, this code is untested:

data want;
  set have (in=firstpass)  have (in=secondpass);
  by id ;
  array _cost_sums {2} _temporary_;

  if first.id then call missing(of _cost_sums{*});
  if firstpass then _cost_sums{pre_post_period}+cost;

  if secondpass;
  pre_cost_sums=_cost_sums{1};
  post_cost_sums=_cost_sums{2};
run;

[Kurt_Bremser]

Similar method, using a double DO loop:

data want;
do until (last.id);
  set have;
  by id;
  pre_cost = sum(pre_cost,ifn(pre_post_period = 1,cost,0));
  post_cost = sum(post_cost,ifn(pre_post_period = 2,cost,0));
end;
do until (last.id);
  set have;
  by id;
  output;
end;
run;

[Kurt_Bremser · Accepted solution]

Or a slight modification of the interleave method:

data want;
set
  have (in=firstpass)
  have (in=secondpass)
;
by id;
retain pre_cost post_cost;
if first.id
then do;
  pre_cost = 0;
  post_cost = 0;
end;
if firstpass
then do;
  if pre_post_period = 1
  then pre_cost + cost;
  else post_cost + cost;
end;
if secondpass;
run;