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;
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.
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.