Help using Base SAS procedures

How to group by monname and keep format on export

Accepted Solution Solved
Reply
Regular Contributor
Posts: 240
Accepted Solution

How to group by monname and keep format on export

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;


Accepted Solutions
Solution
‎03-28-2013 07:23 PM
Respected Advisor
Posts: 3,777

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

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;

View solution in original post


All Replies
Respected Advisor
Posts: 3,777

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

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.

Regular Contributor
Posts: 240

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

HI can you provide an example ? Thank youThank you

Solution
‎03-28-2013 07:23 PM
Respected Advisor
Posts: 3,777

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

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;
Regular Contributor
Posts: 240

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

DATA_NULL_,

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

Valued Guide
Posts: 2,175

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

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

Valued Guide
Posts: 632

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

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 387 views
  • 0 likes
  • 4 in conversation