How could I create new observations in a dataset based on adding, subtracting, etc. across other observations?
In the example below, observation 4 is created so that
Var1 = 'NotTN'
Var2 = sum of all Var2 where Var1^='TN'
Have:
Var1 | Var2 |
---|---|
SC | 1 |
NC | 3 |
TN | 5 |
Want:
Var1 | Var2 |
---|---|
SC | 1 |
NC | 3 |
TN | 5 |
NotTN | 4 |
Thanks!
It depends. How complex can your rules get? That one is fairly straight forward, and here's a hard coded solution. If you need something that changes with your data dynamically this won't work.
data want;
set have end=eof;
retain sum;
if var1 ne 'TN' then sum=sum+var2;
output;
if eof then do;
var1='NotTN';
var2=sum;
output;
end;
run;
It depends. How complex can your rules get? That one is fairly straight forward, and here's a hard coded solution. If you need something that changes with your data dynamically this won't work.
data want;
set have end=eof;
retain sum;
if var1 ne 'TN' then sum=sum+var2;
output;
if eof then do;
var1='NotTN';
var2=sum;
output;
end;
run;
Thanks! The 'end=' option is exactly what I'm looking for.
Also, I needed to put a 0 at the end of the retain line to work:
retain sum 0;
Using sql
data have;
input var1 $ var2;
if var1='TN' then flag=1; else flag=0;
datalines;
SC 1
NC 3
TN 5
;
proc sql;
select var1,var2 from have
union
select "NotTN",sum(var2) from have
where flag=0
order by var2;
quit;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.