Count Distinct Problem because of PlayDate

[sdang]

Hi,

I formatted the column, PlayerDayDate (its original structure is datetime variable like this ,26NOV2014:00:00:00.000) to DTMONYY5 so I can do a summary function Count_Distinct based on GuestNumbers for each month in a year.  The left table is my set-up and right table are the result.  However, the second table had the Count-Distinct per day of the month, not for one whole month.  How do I fix that so it can aggregated the Count_Distinct in Query per month instead per day.

PlayDayDate Count_Distinct_of_GuestNumber

JAN12  645

JAN12  598

JAN12  438

JAN12  380

JAN12  412

JAN12  500

JAN12  593

JAN12  524

JAN12  383

JAN12  403

JAN12  393

JAN12  477

JAN12  544

JAN12  596

JAN12  583

JAN12  492

JAN12  388

JAN12  392

JAN12  425

JAN12  537

JAN12  558

JAN12  527

JAN12  436

JAN12  433

JAN12  413

JAN12  454

JAN12  536

JAN12  613

JAN12  569

JAN12  417

JAN12  461

FEB12  456

FEB12  499

FEB12  594

FEB12   648

[Reeza]

If you're using SQL then the easiest method is to create a new field that is the month by using put(playdate, monyy7.) and then use that field in your group by clause.

PROC SQL does not recognize formats when aggregating data

[sdang]

Thanks you replying but I tried your method and the new column came out as blank.

[ballardw · Accepted solution]

Since your playdate is a Datetime variable then

put(playdate, dtmonyy7.)

to put the date part of the variable as month and year.

[sdang]

Thank you so much. You solved my problem. This will help me a lot in future reports.