topic Re: proc sql table - how to group DATETIME20 format into month or year in SAS Programming

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

jeremy4 — Mon, 05 Aug 2019 15:10:10 GMT

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;

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

ballardw — Mon, 05 Aug 2019 15:20:50 GMT

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.

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

Tom — Mon, 05 Aug 2019 15:57:45 GMT

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;

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

PGStats — Mon, 05 Aug 2019 18:10:43 GMT

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;