Desktop productivity for business analysts and programmers

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

Reply
Frequent Contributor
Posts: 94

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

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

Occasional Contributor
Posts: 5

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

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.

Frequent Contributor
Posts: 94

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

Thank you...that worked!

Super Contributor
Posts: 1,636

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

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;

Community Manager
Posts: 2,889

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

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

Ask a Question
Discussion stats
  • 4 replies
  • 6684 views
  • 1 like
  • 4 in conversation