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

Midi — Tue, 24 Sep 2019 15:55:09 GMT

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.

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

Reeza — Tue, 24 Sep 2019 16:12:28 GMT

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.

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

topic Re: How to Make a Pivot Table of a Table and doing the sum of it's rows ? in SAS Programming

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

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