How to group by monname and keep format on export

[BETO]

Hi,

I have a data step where I'm converting a date/time field into just date and formatting it to monname5. ( I tried monyy5.)

in the second table I would like to group by the month but it seems like even though its a month it still has days same number if it was days.

in addition when I export it out to excel it converts the monname format back to a muddy format ...enclose is a snapshot of the script ...thanks for your assistance

DATA DeActive ;

set DeActive ;

DATE=datepart(TranDate);

FORMAT DATE monyy7.;

RUN;

proc sql;

CREATE TABLE CCMS AS

(SELECT

Atmcodenumber,

Date,

AVG(DispCashAmt) As Avg_DispCashAmt ,

AVG(DispCashCnt)As Avg_DispCashCnt ,

Avg(CurrInitAmt) As Avg_Replen,

Avg(CashAddAmt) As Avg_Cash_Add

FROM DeActive   

group by DATE,Atmcodenumber

);

run;

[data_null__]

I don't know if GROUP BY uses the formatted value of DATE or the internal value or if you can control it.

I do know that PROC SUMMARY will use the formatted value of date by default.  I prefer SUMMARY/MEANS for decriptive statistics over SQL and the code is usually shorted.

When the data get to EXCEL you will need to tell EXCEL how you want the date field formatted.

[BETO]

HI can you provide an example ? Thank youThank you

[data_null__ · Accepted solution]

This is pretty close.  It is untested.

proc summary nway missing data=deactive;
   class date atmcodenumber;
   var DispCashAmt DispCashCnt CurrInitAmt CashAddAmt;
   output out=ccms mean=;
   run;

[BETO]

DATA_NULL_,

Thank you so much for your assistance I was able to make it work...I have never used proc summary before...

[Peter_C]

If you  pass a date formatted as monname to excel through ods tagsets.excelxp you can get excel to present it as a month name with the excel format "mmmm". If you use "mmm" you get the effect of sas format monname3.

peterC

[ArtC]

Formats do not change how the value is stored. they control how the value is displayed.  If you want the month name to appear in Excel, try converting it to a character value and pass the character string.

moname = put(date, monname5.);