topic Re: Count Distinct Problem because of PlayDate in SAS Enterprise Guide

Count Distinct Problem because of PlayDate

sdang — Mon, 22 Jun 2015 21:26:34 GMT

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

Re: Count Distinct Problem because of PlayDate

Reeza — Mon, 22 Jun 2015 21:54:47 GMT

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

Re: Count Distinct Problem because of PlayDate

sdang — Mon, 22 Jun 2015 22:08:15 GMT

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

Re: Count Distinct Problem because of PlayDate

ballardw — Mon, 22 Jun 2015 22:13:03 GMT

Since your playdate is a Datetime variable then

put(playdate, dtmonyy7.)

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

Re: Count Distinct Problem because of PlayDate

sdang — Mon, 22 Jun 2015 22:17:12 GMT

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