Column schema by another column

[ziv]

Hi I have the HAVE column and I need the NEED column
which is summed every time HAVE changes

thank you;

HAVE	need
A
A
A
A
A
B	A
B	A
B	A
B	A
B	A
B	A
B	A
C	A+B
C	A+B
C	A+B
D	A+B+C
D	A+B+C

[LinusH]

Not sure about the use case here, but you could start with

• SET BY have
• retaining need,
• assign need new value when first.have is true.

[ziv]

I didn't really understand that much

[ballardw]

Are the values of A, B and C constant? For example, is A always 3? This is crucial because you have different numbers of A, B, C and D and if any of A or B have different values then you need to provide some vary complex rules about which value of A is paired with which value of B and/or C.

Hint: if values are to be SUMMED then provide example numeric values.

[Tom]

Please provide actual example data and expected results. 

Are those supposed to be character strings?  So you should be able to use BY group processing and LAG().

data have;
 input existing $ @@;
cards;
A A A A A
B B B B B B B
C C C
D D
;
 
data want;
  set have;
  by existing notsorted;
  lag_existing=lag(existing);
  length new_variable $10 ;
  if first.existing then new_variable=catx('+',new_variable,lag_existing);
  retain new_variable ;
  drop lag_existing;
run;

If those are supposed to be numbers in looks like a running SUM.

data have;
 input existing  @@;
cards;
2 2 2 2 2
1 1 1 1 1 1 1
3 3 3 
5 5 
;

data want;
  set have;
  by existing notsorted;
  lag_existing=lag(existing);
  length new_variable 8 ;
  if first.existing then new_variable=sum(new_variable,lag_existing);
  retain new_variable ;
  drop lag_existing;
run;

Tom_0-1676929249387.pngTom_1-1676929364564.png