topic Re: Make a new column with frequency/count in New SAS User

Make a new column with frequency/count

mmea — Thu, 31 Dec 2020 11:04:24 GMT

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

Re: Make a new column with frequency/count

PaigeMiller — Thu, 31 Dec 2020 12:18:20 GMT

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.

Re: Make a new column with frequency/count

mmea — Thu, 31 Dec 2020 12:42:54 GMT

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

Re: Make a new column with frequency/count

PaigeMiller — Thu, 31 Dec 2020 14:14:27 GMT

@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.

Re: Make a new column with frequency/count

novinosrin — Thu, 31 Dec 2020 15:14:06 GMT

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;

Re: Make a new column with frequency/count

anming — Thu, 31 Dec 2020 22:19:10 GMT

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.

Re: Make a new column with frequency/count

PaigeMiller — Thu, 31 Dec 2020 22:32:01 GMT

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.

Re: Make a new column with frequency/count

ballardw — Fri, 01 Jan 2021 16:45:09 GMT

@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?

Re: Make a new column with frequency/count

anming — Thu, 31 Dec 2020 22:51:49 GMT

yes. I am trying to provide an alternative solution.

Re: Make a new column with frequency/count

PaigeMiller — Fri, 01 Jan 2021 11:46:16 GMT

@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.

Re: Make a new column with frequency/count

PaigeMiller — Fri, 01 Jan 2021 11:46:47 GMT

@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.