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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.