topic Re: How to group by monname and keep format on export in SAS Procedures

How to group by monname and keep format on export

BETO — Thu, 28 Mar 2013 18:10:21 GMT

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;

Re: How to group by monname and keep format on export

data_null__ — Thu, 28 Mar 2013 18:53:28 GMT

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.

Re: How to group by monname and keep format on export

BETO — Thu, 28 Mar 2013 22:56:35 GMT

HI can you provide an example ? Thank youThank you

Re: How to group by monname and keep format on export

data_null__ — Thu, 28 Mar 2013 23:23:09 GMT

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; 

Re: How to group by monname and keep format on export

ArtC — Fri, 29 Mar 2013 18:32:32 GMT

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.);

Re: How to group by monname and keep format on export

BETO — Fri, 29 Mar 2013 19:22:30 GMT

DATA_NULL_,

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

Re: How to group by monname and keep format on export

Peter_C — Fri, 29 Mar 2013 20:40:44 GMT

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