topic Re: Column schema by another column in SAS Programming

Column schema by another column

ziv — Mon, 20 Feb 2023 13:40:46 GMT

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

Re: Column schema by another column

LinusH — Mon, 20 Feb 2023 14:00:04 GMT

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.

Re: Column schema by another column

ziv — Mon, 20 Feb 2023 14:01:37 GMT

I didn't really understand that much

Re: Column schema by another column

ballardw — Mon, 20 Feb 2023 16:34:47 GMT

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.

Re: Column schema by another column

Tom — Mon, 20 Feb 2023 21:43:02 GMT

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;