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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: