topic Convert Input Data (dd-mm-yyyy) to Output Data (mmyyyy) in SAS Enterprise Guide

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

jen123 — Sun, 13 Nov 2011 14:11:18 GMT

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

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

Joeldw — Sun, 13 Nov 2011 22:05:03 GMT

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.

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

jen123 — Mon, 14 Nov 2011 15:47:05 GMT

Thank you...that worked!

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

Linlin — Mon, 14 Nov 2011 19:10:15 GMT

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;

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

ChrisHemedinger — Mon, 21 Nov 2011 20:01:48 GMT

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