topic Re: How to add up values (with 0 and non-0 values) in a column in SAS Programming

How to add up values (with 0 and non-0 values) in a column

CynthiaWei — Wed, 13 Mar 2019 12:08:50 GMT

Hi SAS Pros,

I want to create a new variable---Apple_Count_by_Name. For example, each day the teach gives students a different type of fruit. If a student receives an apple, then it is a Yes=1, if not, then is No=0. I need to know how many apples a student has from 1/2 to 1/9. Please ignore the absence dates for some students.

What I have"

Name	Date	Fruit	Is_it_an_apple
Tom	2-Jan	apple	1
Tom	3-Jan	apple	1
Tom	4-Jan	orange	0
Tom	5-Jan	pear	0
Tom	6-Jan	peach	0
Tom	7-Jan	blueberry	0
Tom	8-Jan	blueberry	0
Tom	9-Jan	apple	1
Mike	2-Jan	orange	0
Mike	3-Jan	pear	0
Mike	4-Jan	apple	1
Mike	5-Jan	apple	1
Mike	6-Jan	blueberry	0
Megan	2-Jan	apple	1
Megan	3-Jan	orange	0
Megan	4-Jan	pear	0
Lily	2-Jan	apple	1
Lily	3-Jan	apple	1
Lily	4-Jan	blueberry	0
Lily	5-Jan	apple	1
Lily	6-Jan	orange	0
Lily	7-Jan	pear	0

What I want

Name	Date	Fruit	Is_it_an_apple	Apple_Count_by_Name
Tom	2-Jan	apple	1	3
Tom	3-Jan	apple	1	3
Tom	4-Jan	orange	0	3
Tom	5-Jan	pear	0	3
Tom	6-Jan	peach	0	3
Tom	7-Jan	blueberry	0	3
Tom	8-Jan	blueberry	0	3
Tom	9-Jan	apple	1	3
Mike	2-Jan	orange	0	2
Mike	3-Jan	pear	0	2
Mike	4-Jan	apple	1	2
Mike	5-Jan	apple	1	2
Mike	6-Jan	blueberry	0	2
Megan	2-Jan	apple	1	1
Megan	3-Jan	orange	0	1
Megan	4-Jan	pear	0	1
Lily	2-Jan	apple	1	3
Lily	3-Jan	apple	1	3
Lily	4-Jan	blueberry	0	3
Lily	5-Jan	apple	1	3
Lily	6-Jan	orange	0	3
Lily	7-Jan	pear	0	3

Thank you in advance for any help with this question!

All the best,

Re: How to add up values (with 0 and non-0 values) in a column

PeterClemmensen — Wed, 13 Mar 2019 12:21:23 GMT

data have;
input Name $	Date $	Fruit $	Is_it_an_apple;
datalines;
Tom	2-Jan	apple	1
Tom	3-Jan	apple	1
Tom	4-Jan	orange	0
Tom	5-Jan	pear	0
Tom	6-Jan	peach	0
Tom	7-Jan	blueberry	0
Tom	8-Jan	blueberry	0
Tom	9-Jan	apple	1
Mike	2-Jan	orange	0
Mike	3-Jan	pear	0
Mike	4-Jan	apple	1
Mike	5-Jan	apple	1
Mike	6-Jan	blueberry	0
Megan	2-Jan	apple	1
Megan	3-Jan	orange	0
Megan	4-Jan	pear	0
Lily	2-Jan	apple	1
Lily	3-Jan	apple	1
Lily	4-Jan	blueberry	0
Lily	5-Jan	apple	1
Lily	6-Jan	orange	0
Lily	7-Jan	pear	0
;

proc sql;
   create table want as
   select *,
          sum(Is_it_an_apple)
   from have
   group by name;
quit;

Re: How to add up values (with 0 and non-0 values) in a column

RW9 — Wed, 13 Mar 2019 12:24:26 GMT

This is exactly the same question as from:

https://communities.sas.com/t5/SAS-Programming/How-to-count-actual-counts-in-a-sequence-by-ID/m-p/542717#M149971

For which you will get exactly the same answers, a simple change from count() to sum() is what you are looking for.

Re: How to add up values (with 0 and non-0 values) in a column

PeterClemmensen — Wed, 13 Mar 2019 12:25:26 GMT

Alternatively, you can do this. This preserves the original order in the data set

data want;

   do until (last.name);
      set have;
      by Name notsorted;
      Apple_Count_by_Name=sum(Apple_Count_by_Name, Is_it_an_apple);
   end; 
 
   do until (last.Name);
      set have;
      by Name notsorted;
      output;
   end;

run;

Re: How to add up values (with 0 and non-0 values) in a column

CynthiaWei — Wed, 13 Mar 2019 12:54:22 GMT

I appreciate your response very much!

But, how to create a variable named Apple_Count_by_Name, a variable name that I want, in the step that you just told me.

Best,

Re: How to add up values (with 0 and non-0 values) in a column

CynthiaWei — Wed, 13 Mar 2019 12:56:23 GMT

Thank you very much! May I know what is "last" in the do until statement? And, there is a "." in between last and name?

Best,

Re: How to add up values (with 0 and non-0 values) in a column

PeterClemmensen — Wed, 13 Mar 2019 12:57:37 GMT

Like this

proc sql;
   create table want as
   select *,
          sum(Is_it_an_apple) as Apple_Count_by_Name
   from have
   group by name;
quit;

Re: How to add up values (with 0 and non-0 values) in a column

ballardw — Wed, 13 Mar 2019 15:35:18 GMT

@CynthiaWei wrote:
Thank you very much! May I know what is "last" in the do until statement? And, there is a "." in between last and name?

Best,

When a BY statement is used in a data step SAS creates temporary variables to indicate that a specific value is the first or last of the by group variable. So FIRST.variable or LAST.variable is a true/false value that is true (or 1) when the observation is the first one with that value. The dot indicates the use of this special feature instead of expecting or creating a variable named firstvariable or lastvariable.

Multiple variables on the BY statement means each variable gets its own first and last indicators.

If First.variable = Last.variable then there is only one value in the by group.