BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sdang
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

4 REPLIES 4
Reeza
Super User

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

sdang
Quartz | Level 8

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

asd.PNG

ballardw
Super User

Since your playdate is a Datetime variable then

put(playdate, dtmonyy7.)

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

sdang
Quartz | Level 8

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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