topic Re: How to calculate sum on few items in SAS Programming

How to calculate sum on few items

noda6003 — Sat, 18 Apr 2020 10:46:17 GMT

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

Re: How to calculate sum on few items

ed_sas_member — Sat, 18 Apr 2020 11:02:43 GMT

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,

Re: How to calculate sum on few items

ed_sas_member — Sat, 18 Apr 2020 11:08:07 GMT

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;

Re: How to calculate sum on few items

Kurt_Bremser — Sat, 18 Apr 2020 11:15:22 GMT

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;

Re: How to calculate sum on few items

andreas_lds — Sat, 18 Apr 2020 11:25:08 GMT

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;