topic Re: Create count variable by ID in New SAS User

Create count variable by ID

Mikkel_madsen — Fri, 12 Jun 2020 12:15:40 GMT

Hi,

I have the dataset "have" where I want to create a new variable.

The new variable should count observations with the same ID. I have:

ID	GPS	Open	Store
1	89503	Y	1
1	12353	Y	1
1	06834	N	2
2	95736	N	2
2	01475	Y	3
3	64758	Y	4

The new variable count should be like:

ID	GPS	Open	Store	Count
1	89503	Y	1	3
1	12353	Y	1	3
1	06834	N	2	3
2	95736	N	2	2
2	01475	Y	3	2
3	64758	Y	4	1

Hope you can help me! Thank you.

Kind regards MM

Re: Create count variable by ID

novinosrin — Fri, 12 Jun 2020 12:19:57 GMT


data have;
input ID	GPS $	Open $	Store;
cards;
1	89503	Y	1
1	12353	Y	1
1	06834	N	2
2	95736	N	2
2	01475	Y	3
3	64758	Y	4
;

proc sql;
create table want as
select *, count(id) as count
from have
group by id
order by id,store;
quit ;

Re: Create count variable by ID

ed_sas_member — Fri, 12 Jun 2020 12:18:52 GMT

Hi @Mikkel_madsen

You can use a PROC SQL for example:

proc sql;
	create table want as
	select *, count(*) as count
	from have
	group by id;
run;

Best,

Re: Create count variable by ID

novinosrin — Fri, 12 Jun 2020 12:24:18 GMT

Hi @Mikkel_madsen I am sharing another smart stuff taught by genius @mkeintz to whom I owe a lot of my learning


data have;
input ID	GPS $	Open $	Store;
cards;
1	89503	Y	1
1	12353	Y	1
1	06834	N	2
2	95736	N	2
2	01475	Y	3
3	64758	Y	4
;

data want;
 set have(in=a) have(in=b);
 by id;
 retain count;
 if first.id then count=0;
 if a then count=sum(count,1);
 if b;
run;

Re: Create count variable by ID

mkeintz — Sat, 13 Jun 2020 02:50:42 GMT

And, in the pursuit of terminal cuteness, one can:

data have;
input ID	GPS $	Open $	Store;
cards;
1	89503	Y	1
1	12353	Y	1
1	06834	N	2
2	95736	N	2
2	01475	Y	3
3	64758	Y	4
;

data want;
 set have(in=countme) have(in=keepme);
 by id;
 if first.id then count=0;
 count+countme;
 if keepme;
run;

Sorry ... couldn't resist.