topic Sincerely asking about how to count in this scenario please in SAS Programming

Sincerely asking about how to count in this scenario please

septemberbulb — Tue, 30 May 2023 17:52:26 GMT

Hello,

I am trying to count the number of persons based on Variable Account All below:

	#
1 Person	X
2 Person	Y
3+ Person	Z

Data is like this layout:

If there is a 2 duplicate account all, it means it has 2 personal ID (vertical view)

if 3 duplicates, then 3 personal ID,

I am not sure how to count this automatically in this situation?

Could anyone help?

Thank you

Account All	Account ID	Person ID
123	123	1234
456	456	4568
456	456	4569
789	789	78910
789	789	78911
789	789	78912
101123	101123	1011213
121314	121314	12131415
131415	.	.

Re: Sincerely asking about how to count in this scenario please

A_Kh — Tue, 30 May 2023 19:31:23 GMT

You can process data using BY -group in data step, where first.variable and last.variable determine the start and the end counts of your accounts (running counts).

data have;
input Account_All	Account_ID	Person_ID;
cards; 
123	123	1234
456	456	4568
456	456	4569
789	789	78910
789	789	78911
789	789	78912
101123	101123	1011213
121314	121314	12131415
131415	.	.
;
proc print;run; 

data want; 
	set have;
	by account_all account_id;
	if first.account_id then Total_person=1;
	else Total_person+1; 
proc print;run;

In case you need only totals by accoint_id, use proc sql;

proc sql;
	create table want as
		select*, count(*) as Total_person
			from have
		group by account_all, account_id;
quit;

Re: Sincerely asking about how to count in this scenario please

Reeza — Tue, 30 May 2023 20:24:09 GMT

Please, when you post a problem, make sure the solution shown aligns with the data shown. So for the sample data shown, what exactly would be the expected output.

I'm interpreting the question differently than @A_Kh for example.

data have;
input Account_All   Account_ID  Person_ID;
cards; 
123 123 1234
456 456 4568
456 456 4569
789 789 78910
789 789 78911
789 789 78912
101123  101123  1011213
121314  121314  12131415
131415  .   .
;

proc format;
value dup_format
1  = '1'
2 = '2'
3 - high = '3+';
run;

proc sql;
create table temp as
select account_all, count(distinct person_id) as unique_persons
from have
group by account_all;
quit;

proc freq data=temp;
table unique_persons;
format unique_persons dup_format.;
run;

Results:

@septemberbulb wrote:

Hello,

I am trying to count the number of persons based on Variable Account All below:

#

1 Person X

2 Person Y

3+ Person Z

Data is like this layout:

If there is a 2 duplicate account all, it means it has 2 personal ID (vertical view)

if 3 duplicates, then 3 personal ID,

I am not sure how to count this automatically in this situation?

Could anyone help?

Thank you

Account All Account ID Person ID

123 123 1234

456 456 4568

456 456 4569

789 789 78910

789 789 78911

789 789 78912

101123 101123 1011213

121314 121314 12131415

131415 . .