proc sql table - how to group DATETIME20 format into month or year

[jeremy4]

Hi,

I have created a proc sql table and want to output the results into Excel to create a pivot table - however, the DATETIME20 format means that it is difficult to interpret when the pivot table is created. Is there a way to make sure that the outputs are converted - would it be possible to edit my code to create two different versions:

1. By month (so 14JUN2011:00:00:00 would become JUN2011 in the 'Output Data')

2. By year (so 14JUN2011:00:00:00 would become 2011 in the 'Output Data')

Code

proc sql;
      create table date_by_segment as
      select A.DATE_OPEN,                            /* How to do I edit the code to create the two versions (1. by month 2. by year)? */

                A.ID,
                B.segment

   from ac.dataset1 as A
   left join ac.dataset2 as B
   on a.id = B.id 

   ;

quit;

[ballardw]

Easiest may be to create an actual DATE value:  datepart(date_open) as date

Use the MONYY7. format display the month year and the Year4 format to display just the Year as needed.

Use the appropriate format in a report procedure such as Report or Tabulate and don't worry so  much about the SQL "Group by".

The little bit I see of your example values you may actually have been bitten by one of the, IMHO, stupid Microsoft defaults of making date values into datetimes with all hour=0 minute=0 and second=0. If that is the case then definitely use datepart to create a date value instead of datetime.

[Tom]

You can use the YEAR() and DATEPART() functions to generate a numeric variable with the YEAR from your DATETIME value.

For the MONTH level data you either create a date value (or datetime value if you want) that uses a constant day of the month, like the first of the month.  Or create a character variable.

proc sql;
  create table date_by_segment as
  select 
         A.ID
       , B.segment
       , A.DATE_OPEN
       , year(datepart(A.DATE_OPEN)) as YEAR
       , intnx('dtmonth',A.DATE_OPEN,0,'b') as MONTH_DATETIME format=datetime20.
       , put(A.DATE_OPEN,dtyear4.) as YEAR_CHAR
       , put(A.DATE_OPEN,dtmonyy7.) as MONTH_CHAR
   from ac.dataset1 as A
   left join ac.dataset2 as B
   on a.id = B.id 
  ;
quit;

[PGStats]

Try this:

proc sql;
create table date_by_segment as
select 
    A.DATE_OPEN,
    intnx("DTMONTH", A.DATE_OPEN, 0) as DATE_OPEN_MONTH, 
    intnx("DTYEAR", A.DATE_OPEN, 0) as DATE_OPEN_YEAR, 
    A.ID,
    B.segment
from 
    ac.dataset1 as A left join 
    ac.dataset2 as B on a.id = B.id 
;
quit;