DATA Step, Macro, Functions and more

Count value across variables

Accepted Solution Solved
Reply
Regular Learner
Posts: 1
Accepted Solution

Count value across variables

Could some one should help me about this.

data dt1;

input a1 a2 a3;

cards;

1  2    3

4  4   4

;

run;

 

output:

a1  a2  a3  flag  count

1    2    3   yes     2

4    4   4    no      0

 

if the value is changed across observation then flag yes else no, if there is a change how many times its changed(count)


Accepted Solutions
Solution
‎08-06-2017 04:11 AM
Super User
Posts: 7,808

Re: Count value across variables

Posted in reply to krishna2121
data want;
set dt1;
array vars {*} a1-a3;
count = 0;
flag = 'no';
do i = 2 to dim(vars);
  if vars{i} ne vars{i-1}
  then do;
    count = count + 1;
    flag = 'yes';
  end;
end;
drop i;
run;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Solution
‎08-06-2017 04:11 AM
Super User
Posts: 7,808

Re: Count value across variables

Posted in reply to krishna2121
data want;
set dt1;
array vars {*} a1-a3;
count = 0;
flag = 'no';
do i = 2 to dim(vars);
  if vars{i} ne vars{i-1}
  then do;
    count = count + 1;
    flag = 'yes';
  end;
end;
drop i;
run;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super Contributor
Posts: 440

Re: Count value across variables

Posted in reply to KurtBremser

This is an interesting question and to be honest I was a little surprised to realise their wasn't an inbuilt function to give us the number of distinct values across a range of variables.

 

There's only one change I'd suggest to Kurt's answer - if you don't add the length statement then the value of the flag variable is truncated for the second observation so:

 

data want;
set dt1;
length flag $3;
array vars {*} a1-a3;
count = 0;
flag = 'no';
do i = 2 to dim(vars);
  if vars{i} ne vars{i-1}
  then do;
    count = count + 1;
    flag = 'yes';
  end;
end;
drop i;
run;

The other thing that occcured to me was how would you want missing values treated?

 

 

Super User
Posts: 7,808

Re: Count value across variables

Posted in reply to ChrisBrooks

Yep, good catch. Either use a length statement or

flag = 'no ';

to avoid truncation.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
PROC Star
Posts: 283

Re: Count value across variables

Posted in reply to ChrisBrooks

data dt1;

input a1-a5;

cards;

1  2    3 4 5 4

4  4   4 2 3 4

4    4    4    3    4 4

4    4.1  4.2  4    4    4

4 4 4 4 4

;

run;

 

 

data want1;

set dt1;

array num _numeric_;

count=0;

if std(of num(*))=0 then flag='N';

else do;

flag='Y';

call sortn(of num(*));

do _n_=1 to dim(num)-1;

if std(num(_n_),num(_n_+1))= 0 then continue;

else count+1;

end;

end; 

run;

 

/*Without callsortn- slightly different count*/

data want1;

set dt1;

array num _numeric_;

count=0;

if std(of num(*))=0 then flag='N';

else do;

flag='Y';

do _n_=1 to dim(num)-1;

if std(num(_n_),num(_n_+1))= 0 then continue;

else count+1;

end;

end; 

run;

 

Super User
Posts: 7,808

Re: Count value across variables

Posted in reply to ChrisBrooks

ChrisBrooks wrote:

This is an interesting question and to be honest I was a little surprised to realise their wasn't an inbuilt function to give us the number of distinct values across a range of variables.

 


I guess this is because one would usually solve such problems by transposing into a long format (or keeping the data in such a format from the start) and using proc means/summary/freq or SQL summary functions, or (in this case) find differences using lag().

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 266 views
  • 3 likes
  • 4 in conversation