BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
huhuhu
Obsidian | Level 7

Hello everyone,

 

I'm wondering if sas could count distinct values in each row. For example, my data looks similar like:

 

ID Col1 Col2 Col3 Col4

1   12     12    13     14

2    23    34    34     34

3    55    67    .        45

4    33    33    33     33

 

I woud like to have the count of unique values that each ID has, something like:

ID     # Uniquevalues

1        3

2        2

3        3

4        1

 

I appreciate any ideas that may help. Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
stat_sas
Ammonite | Level 13

Another way using transpose with sql:

 

proc transpose data=have out=want;
by id;
run;

 

proc sql;
create table uniq as
select id, count(distinct col1) as Unique_values from want group by id;
quit;

View solution in original post

14 REPLIES 14
Astounding
PROC Star

Assumptions make a difference in how complex the code becomes.  I'm going to assume:

  • You may have missing values in your data
  • The sample data is representative, but you actually have more variables than 4. 
  • Since the original variables are not being kept in the output, they can be changed by the program

Here's one way to go about it (assuming you actually have 50 variables):

 

data want;

set have;

array col {50};

call sortn (of col1-col50);

unique_vals = (col1 > .);

do _n_=2 to 50;

     if col{_n_} > col{_n_-1} then unique_vals + 1;

end;

keep id unique_vals;

run;

 

If the assumptions should be different, we can always adjust.

 

Good luck.

huhuhu
Obsidian | Level 7

Thank you for your input and assumptions. I tried your codes, but in the new dataset it showed all zeros in column " unique_vals". I just change the number 50 to exact columns I have. Any suggestions?

 

Those assumptions are correct. I also would like to be clear that the numbers are randomly organized and not in any orders in each row (like NOT col4>col3>col2>col1); at the same time, there could be more than one missing values in each row, which can be at any columns.

 

Thank you,

Astounding
PROC Star

OK, the program is short enough that you could probably post your log.  It should be an easy fix.

stat_sas
Ammonite | Level 13

Another way using transpose with sql:

 

proc transpose data=have out=want;
by id;
run;

 

proc sql;
create table uniq as
select id, count(distinct col1) as Unique_values from want group by id;
quit;

huhuhu
Obsidian | Level 7

smart way!

Haikuo
Onyx | Level 15

Another array option: 

data have;
	input ID Col1 Col2 Col3 Col4;
	cards;
1   12     12    13     14
2    23    34    34     34
3    55    67    .        45
4    33    33    33     33
;
run;

data want;
	set have;
	array temp(50) _temporary_;
	array col(50) col1-col50;

	do i=1 to dim(col);
		if col(i) not in temp then
			temp(i)=col(i);
	end;

	ct=n(of temp(*));
	call missing (of temp(*));
	keep id ct;
run;
huhuhu
Obsidian | Level 7

It works! Thank you!

Ksharp
Super User
It is IML thing.



data have;
	input ID Col1 Col2 Col3 Col4;
	cards;
1   12     12    13     14
2    23    34    34     34
3    55    67    .        45
4    33    33    33     33
5    .    .    .     .
;
run;
proc iml;
use have(keep=id);
read all var {id};
close;

use have(keep=col:);
read all var _num_ into x;
close;

n=countunique(x,'row')-(countmiss(x,'row')^=0);

create want var{id n};
append;
close;

quit;


Haikuo
Onyx | Level 15

@Ksharp

For some reason both of your posts in this thread are truncated. Maybe you can try posting your code as SAS code?

ballardw
Super User

@Haikuo ; I suspect you are using Internet Explorer. IE has some "feature" such that code involving braces does not render correctly from this forum.


@Haikuo wrote:

@Ksharp

For some reason both of your posts in this thread are truncated. Maybe you can try posting your code as SAS code?


 

Haikuo
Onyx | Level 15

Yes, indeed, I was using IE. IMHO, the quality of this forum is not in par with SAS reputation. If I use Chrome, I sometimes have a login issue. Another one is of course,  The notorious Hash colon problem, the colon gets to turn into literal spelling. Wonder will it get ever fixed. 

Ksharp
Super User
Sorry. I can not . It seems that Chinese Government has already block some Java Script at this forum . I can't see the running man icon or any icon above, I even can't edit my response . I have to use HTML code to reply. Fortunately I can click the POST button ,otherwise I have to leave this forum .
Ksharp
Super User
OR Hash Table.




data have;
	input ID Col1 Col2 Col3 Col4;
	cards;
1   12     12    13     14
2    23    34    34     34
3    55    67    .        45
4    33    33    33     33
5    .    .    .     .
;
run;

data want;
 if _n_ eq 1 then do;
  declare hash h();
  h.definekey('k');
  h.definedone();
 end;
set have;
 array x{*} col:;
 do i=1 to dim(x);
  if not missing(x{i}) then do;k=x{i};h.replace();end;
 end;
 n=h.num_items;
 h.clear();
drop i k col:;
run;




huhuhu
Obsidian | Level 7

Thanks to everyone's help! The problem already been solved. 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 14 replies
  • 6239 views
  • 9 likes
  • 6 in conversation