I have data like
Item | value |
LYUI0105 | 10 |
LYUI0114 | 20 |
LYUI0123 | 22 |
LYUI0132 | 22 |
LYUI0141 | 12 |
LYUI0150 | 12 |
LYUI0158 | 35 |
LYUI0165 | 21 |
LYUI0170 | 22 |
LYUI0175 | 112 |
LYUI0201 | 21 |
LYUI0207 | 11 |
and i want a new row with total of
LYUI0105 |
LYUI0114 |
LYUI0123 |
LYUI0132 |
LYUI0141 |
LYUI0150 |
like new_score
Item | value |
LYUI0105 | 10 |
LYUI0114 | 20 |
LYUI0123 | 22 |
LYUI0132 | 22 |
LYUI0141 | 12 |
LYUI0150 | 12 |
LYUI0158 | 35 |
LYUI0165 | 21 |
LYUI0170 | 22 |
LYUI0175 | 112 |
LYUI0201 | 21 |
LYUI0207 | 11 |
New_score |
98
|
Can anyone help me on it
In one data step:
data want;
length Item $9;
set have end=done;
retain new_score 0;
if item le "LYUI0150" then new_score + value;
output;
if done
then do;
item = 'New_score';
value = new_score;
output;
end;
drop new_score;
run;
Hi @noda6003
Here is an approach to do this using PROC SQL and the set operator "UNION", which enables to append the result of to queries:
data have;
infile datalines dlm="09"x;
input Item $ value;
datalines;
LYUI0105 10
LYUI0114 20
LYUI0123 22
LYUI0132 22
LYUI0141 12
LYUI0150 12
LYUI0158 35
LYUI0165 21
LYUI0170 22
LYUI0175 112
LYUI0201 21
LYUI0207 11
;
run;
proc sql;
create table want as
(select *
from have)
union all
(select "New_score", sum(value)
from have
where item in ("LYUI0105", "LYUI0114","LYUI0123",
"LYUI0132","LYUI0141","LYUI0150"))
order by Item;
run;
Best,
Another approach using a PROC MEANS to get the score and the a DATA step to append results:
data have;
infile datalines dlm="09"x;
input Item $ value;
datalines;
LYUI0105 10
LYUI0114 20
LYUI0123 22
LYUI0132 22
LYUI0141 12
LYUI0150 12
LYUI0158 35
LYUI0165 21
LYUI0170 22
LYUI0175 112
LYUI0201 21
LYUI0207 11
;
run;
proc means data=have noprint;
var value;
where item in ("LYUI0105", "LYUI0114","LYUI0123",
"LYUI0132","LYUI0141","LYUI0150");
output out=have_score (keep=value) sum=value;
run;
data want;
length Item $10.;
set have have_score;
if Item = "" then Item="New_score";
run;
In one data step:
data want;
length Item $9;
set have end=done;
retain new_score 0;
if item le "LYUI0150" then new_score + value;
output;
if done
then do;
item = 'New_score';
value = new_score;
output;
end;
drop new_score;
run;
If the data is sorted by "Item":
data SumItems;
input Item $;
datalines;
LYUI0105
LYUI0114
LYUI0123
LYUI0132
LYUI0141
LYUI0150
;
run;
data work.summed;
length Item $ 10;
merge have SumItems(in=sumIt) end=jobDone;
by Item;
retain sum 0;
if sumIt then do;
sum = sum + value;
end;
output;
if jobDone then do;
Item = "New_score";
value = sum;
output;
end;
drop sum;
run;
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.