Desktop productivity for business analysts and programmers

Count Distinct Problem because of PlayDate

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 104
Accepted Solution

Count Distinct Problem because of PlayDate

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.

Capture.PNG

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


Accepted Solutions
Solution
‎06-22-2015 06:13 PM
Grand Advisor
Posts: 10,210

Re: Count Distinct Problem because of PlayDate

Since your playdate is a Datetime variable then

put(playdate, dtmonyy7.)

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

View solution in original post


All Replies
Grand Advisor
Posts: 17,325

Re: Count Distinct Problem because of PlayDate

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 Smiley Sad

Frequent Contributor
Posts: 104

Re: Count Distinct Problem because of PlayDate

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

asd.PNG

Solution
‎06-22-2015 06:13 PM
Grand Advisor
Posts: 10,210

Re: Count Distinct Problem because of PlayDate

Since your playdate is a Datetime variable then

put(playdate, dtmonyy7.)

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

Frequent Contributor
Posts: 104

Re: Count Distinct Problem because of PlayDate

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 349 views
  • 0 likes
  • 3 in conversation