How to Make a Pivot Table of a Table and doing the sum of it's rows ?

[Midi]

Hello Everyone , So I have This Table :

Oss	Client_ID	Code_ID	Type_ID	Mas	FAM_ID
1	23895517	23895517	143	5	30						.
2	23895517	23895518	143	8	25						.
3	23895517	23895519	143	3	15
4	23895517	23895520	141	5	15						.
5	23895522	23895521	139	8	15						.							.
6	23895522	23895522	139	1	26						.							.

7	23895522	23895523	143	5	30
8	23895523	23895524	143	5	30						.
9	23895523	23895525	143	8	25						.
10	23895523	23895526	143	3	15
11	23895524	23895520	141	5	15						.
12	23895524	23895521	139	8	15						.							.
13	23895524	23895522	139	1	26						.							.

And I want To get This Output :

Client_ID	FAM_ID      30	FAM_ID 25	FAM_ID 15	FAM_ID 26
											.
											.
23895517	1	1	3	1
											.
											.							.
											.							.

So , basically what it does is , it looks for every FAM_ID and then counts the distinct Mas for every Client .

Any Help Would Be Much Appreciated , Thank you.

[Reeza]

SAS doesn't have procs that do count distinct except for PROC SQL, so first do the distinct calculation and then use PROC FREQ/TABULATE. Do you need a table/data set or do you need a report?

proc sql;
create table distinct_counts as
select client_id, fam_id, count(distinct MAS) as distinct_MAS
from have
group by client_id, fam_id;
quit;

proc transpose data=distinct_counts out=want1 prefix=famID_;
by client_id;
id fam_id;
var distinct_MAS;
run;

proc print data=want1;
run;

---

@Midi wrote:

Hello Everyone , So I have This Table :

Oss	Client_ID	Code_ID	Type_ID	Mas	FAM_ID
1	23895517	23895517	143	5	30						.
2	23895517	23895518	143	8	25						.
3	23895517	23895519	143	3	15
4	23895517	23895520	141	5	15						.
5	23895522	23895521	139	8	15						.							.
6	23895522	23895522	139	1	26						.							.

7	23895522	23895523	143	5	30
8	23895523	23895524	143	5	30						.
9	23895523	23895525	143	8	25						.
10	23895523	23895526	143	3	15
11	23895524	23895520	141	5	15						.
12	23895524	23895521	139	8	15						.							.
13	23895524	23895522	139	1	26						.							.

And I want To get This Output :

Client_ID	FAM_ID      30	FAM_ID 25	FAM_ID 15	FAM_ID 26
											.
											.
23895517	1	1	3	1
											.
											.							.
											.							.

So , basically what it does is , it looks for every FAM_ID and then counts the distinct Mas for every Client .

Any Help Would Be Much Appreciated , Thank you.

---