topic Re: row wise count in SAS Programming

row wise count

soham_sas — Tue, 28 Nov 2017 11:51:25 GMT

I have a data set like below , i want the number of 1 present from var1 to var7 in the count_1 column

E.g Id 1 will have 3 , Id 2 will have 2 and so on ...

data have : ..

IDs	Var1	Var2	Var3	Var4	Var5	var6	Var7	Count_1
1	0	0	0	0	1	1	1
2	0	0	0	0	0	1	1
3	1	0	0	0	0	0	0
4	0	0	1	0	0	0	0
5	0	1	0	1	0	1	0
6	0	0	0	0	0	0	1
7	0	0	1	1	1	0	0
8	0	0	0	0	0	0	0
9	0	0	0	0	0	0	0
10	0	0	0	0	0	0	0
11	0	0	0	0	0	0	0
12	0	0	0	0	0	0	0
13	0	0	0	1	0	1	0
14	0	0	1	1	1	0	0
15	1	1	0	0	0	0	0
16	0	0	0	0	1	1	1
17	0	0	0	0	0	0	0
18	0	0	0	0	0	0	0
19	0	0	1	0	0	0	0
20	0	1	0	0	0	1	0
21	0	0	0	0	0	0	0
22	1	0	0	0	0	1	0
23	0	0	1	1	0	0	0

data want :

IDs	Var1	Var2	Var3	Var4	Var5	var6	Var7	Count_1
1	0	0	0	0	1	1	1	3
2	0	0	0	0	0	1	1	2
3	1	0	0	0	0	0	0	1

Re: row wise count

RW9 — Tue, 28 Nov 2017 11:55:48 GMT

Not tested as not typing that in - post test data in th eform of a datastep in future.

data want;
  set have;
  count_1=sum(of var:);
run;

Re: row wise count

Kurt_Bremser — Tue, 28 Nov 2017 11:57:55 GMT

count_1 = sum(of var1-var7);

Re: row wise count

soham_sas — Tue, 28 Nov 2017 12:01:43 GMT

Hi.
All the observations are 1 so sum function will work. If the observation
are not 1 or any character variable. Then how to perform the count?

Please help...

Re: row wise count

PeterClemmensen — Tue, 28 Nov 2017 12:09:18 GMT

Then put your variables into an array, loop over the array and set a counter to count=count+1 when the variable value meets some criterion..

Re: row wise count

Kurt_Bremser — Tue, 28 Nov 2017 12:09:47 GMT

@soham_sas wrote:
Hi.
All the observations are 1 so sum function will work. If the observation
are not 1 or any character variable. Then how to perform the count?

Please help...

Define an array for your existing variables, and count in a do loop.

array vars{*} var1-var7;
counter_1 = 0;
do i = 1 to dim(vars);
  if not missing(vars{i}) then counter_1 + 1;
end;
drop i;

Re: row wise count

soham_sas — Tue, 28 Nov 2017 12:48:42 GMT

@Kurt_Bremserand @PeterClemmensen thanks for the solutions i tried the below code and its working fine , but i am not getting the logic that how SAS is counting the number of observation as there is no count option/function is used , please explain how the "counter_1 + 1" in the below code the giving the count of observations

data want;
set ds1;
array vars{*} var1-var7;
counter_1 = 0;
do i = 1 to dim(vars);
if (vars{i})=1 then counter_1 + 1;
end;
drop i;
run;

Re: row wise count

RW9 — Tue, 28 Nov 2017 12:56:31 GMT

I have no idea what your posts mean? From your original test data output, the sum should work. If it doesn't post test data in the form of a datastep which illustrates your problem, and what the result should be.

Re: row wise count

Kurt_Bremser — Tue, 28 Nov 2017 12:56:48 GMT

You are not counting observations, you are counting through variables within an observation.

The number of iterations of the do loop is determined by the size of the array, which depends on the number of variables named in the array statement. Basically

-var7

in the array statement sets all this.

Re: row wise count

rajeshalwayswel — Tue, 28 Nov 2017 13:40:12 GMT

Try this one...

data want;
set have;
a1=n(of var:);
run;

Re: row wise count

RW9 — Tue, 28 Nov 2017 13:41:53 GMT

As a tip, use the code window (its the {i} above the post area) to preserve formatting - i.e. would of avoided the smiley face in your code.