Convert Input Data (dd-mm-yyyy) to Output Data (mmyyyy)

[jen123]

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

[Joeldw]

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]

Thank you...that worked!

[Linlin]

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]

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