Make a new column with frequency/count

[mmea]

Hi. If i have a dataset looking like this 

Date	Event
01JAN2020	event1
01JAN2020	event1
01JAN2020	event1
02JAN2020	.
02JAN2020	.

Is it possible to make a new dataset that has a column that counts the same combinations? Like this:

Date	event	count
01JAN2020	event1	3
02JAN2020	.	2

[PaigeMiller]

proc freq data=have;
    tables date*event/missing noprint out=_counts_;
run;
data want;
    merge have _counts_;
    by date event;
run;

This assumes the data set is sorted by DATE and EVENT.

[mmea]

Thanks. i works.

Can i then put it in a proc sql an only get the distinct values.

Because now in my data the same combinations can appear multiple times

[PaigeMiller]

---

@mmea wrote:

Can i then put it in a proc sql an only get the distinct values.

Because now in my data the same combinations can appear multiple times

---

Doesn't PROC FREQ give you the distinct combinations of date and event? What do you mean by the above? Show us what you mean.

[novinosrin]

Somebody who wants SQL solutions is likely my colleague either in RI, MA, CT or PA

data have;
input Date :date9.	Event $;
format date date9.;
cards;
01JAN2020	event1
01JAN2020	event1
01JAN2020	event1
02JAN2020	.
02JAN2020	.
;
proc sql;
 create table want as
 select date, event, count(*) as count
 from have
 group by date, event;
quit;

[anming]

data have;
input Date date9. Event $;
format date date9.;
cards;
01JAN2020 event1
01JAN2020 event1
01JAN2020 event1
01JAN2020 event1
02JAN2020 .
02JAN2020 .
;
run;

proc freq data=have ;
tables date/out=test1;
/* output _counts_; */
run;

data want1;
set test1 (drop=percent);
run;

data want2;
set have;
set want1;
run;

try this.

[PaigeMiller]

Can you actually type a data set that represents the desired output? I thought you did originally, my code produces that data set, but then you asked more questions and seemed to want something else.

So, it's still not clear what result you want, or why you have created the following data set using two SET statements. You have not explained, and I am asking you to please explain, provide details, more than the two words "try this", and SHOW us what you want if it is different than what you asked for in your first message. It's also not clear to me why you have removed the MISSING option from my TABLES statement in PROC FREQ, as I would think you want that based on your original explanation.

[ballardw]

---

@PaigeMiller wrote:

Can you actually type a data set that represents the desired output? I thought you did originally, my code produces that data set, but then you asked more questions and seemed to want something else.

So, it's still not clear what result you want, or why you have created the following data set using two SET statements. You have not explained, and I am asking you to please explain, provide details, more than the two words "try this", and SHOW us what you want if it is different than what you asked for in your first message. It's also not clear to me why you have removed the MISSING option from my TABLES statement in PROC FREQ, as I would think you want that based on your original explanation.

---

@PaigeMiller 

I think you responded to @anming thinking that was the OP @mmea . I believe anming is proposing an alternate solution?

[anming]

yes. I am trying to provide an alternative solution.

[PaigeMiller]

---

@anming wrote:

yes. I am trying to provide an alternative solution.

---

Alternative in what way? It doesn't appear to give the same answer as was requested in the original message.

[PaigeMiller]

---

@ballardw wrote:

---

@PaigeMiller wrote:

Can you actually type a data set that represents the desired output? I thought you did originally, my code produces that data set, but then you asked more questions and seemed to want something else.

So, it's still not clear what result you want, or why you have created the following data set using two SET statements. You have not explained, and I am asking you to please explain, provide details, more than the two words "try this", and SHOW us what you want if it is different than what you asked for in your first message. It's also not clear to me why you have removed the MISSING option from my TABLES statement in PROC FREQ, as I would think you want that based on your original explanation.

---

@PaigeMiller 

I think your responded to @anming thinking that was the OP @mmea . I believe anming is proposing an alternate solution?

---

My apologies to @mmea for the mis-understanding.