BookmarkSubscribeRSS Feed
jen123
Fluorite | Level 6

One of the columns i(WORK_DATE) n the dataset is in dd-mm-yyyy format.  The dataset shows the number of items worked by employees.  I would like to group the total items worked by month (format:  mmyyyy).

When I import the dataset I changed the WORK_DATE column format from dd-mm-yyyy to mmyyyy.  Then I used a query builder to group by WORK_DATE - here are the 3 columns for the output:

employee_name

count_of_employee_name

work_date

This did not work as it still grouped by dd-mm-yyyy NOT mmyyyy.

How can I get the query to group by month (mmyyyy)  by employee?  This is the format I am trying to get:

Employee_Name            Count_of_employee_name             work_date

Count_of_employee_name = total number of items worked in a given month

work_date = MonthYear

4 REPLIES 4
Joeldw
Calcite | Level 5

In your query, you could calculate a new variable using the INTNX function, and use that for your grouping instead of the original variable. In your case,

intnx('month',work_date,0)

Where 'month' is the interval type, and 0 is the interval since you want the same calendar month as the variable you started with. Note, work_date must be a date, not a datetime variable. The result will actually be a value equal to the 1st of each month, but then you can also apply the format you chose (mmyyyy) to this calculated variable so the output doesn't show the day of month, just month and year.

jen123
Fluorite | Level 6

Thank you...that worked!

Linlin
Lapis Lazuli | Level 10

data have;

informat date ddmmyy10. ;

format work_date monyy7.;

input name $ date item ;

work_date=input(put(date,monyy7.),monyy7.);

cards;

aa 31122005     1000      

aa 20122005     3000      

aa 20022006     3000      

bb 20082005     1000      

bb 26082005     2000

cc 26022005     1500

dd 03052005      500

;

run;

proc sql;

  create table want as select name, work_date, sum(item) as Count_of_employee_name

  from have

  group by name,work_date

  order by 1,2;

quit;

ChrisHemedinger
Community Manager

Depending on your purpose, you might be able to get away with simply overriding the format within your report (keeping the original format as is).  See examples at:

http://blogs.sas.com/content/sasdummy/2011/11/15/do-you-mind-if-we-dance-with-your-dates/

Chris

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.

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
  • 11914 views
  • 1 like
  • 4 in conversation