Hi guys,
I'm trying to change the date from DATETIME to date (with datepart) and then extract only month and year by changing format to MONYY7. I want to do so in order to count certain ID for each month. The issue is, SAS treats this date as previous dates, but it gives me the month, in example while using distinct I will get two separate lines with MAY 2016 and again MAY 2016 because days might differ. How to avoid that, to sum things up?
please help!
PROC SQL;
CREATE TABLE WORK.QUERY AS
SELECT DISTINCT
datepart(t1.EXPIRE_DATE,9) FORMAT=MONYY7.,
t1.CONT_ID
FROM CONTRACT t1
GROUP BY t1.EXPIRE_DATE;
QUIT;
@pkonopnicki wrote:
KurtBremser when I remove '9' or use your code starting with intnx it still gives me the same result : /
If you still use
group by expire_date;
that is the culprit.
I adapted my code
proc sql;
create table want as
select distinct put(datepart(expire_date),monyy7.) as exp_month, count(ID)
from have
group by calculated exp_month
;
quit;
The format changes only the way the values are displayed, the values stay the same.
You have to create a value that represents only the month, like
data have;
expire_date = '22nov2016:13:53:24'dt;
output;
expire_date = '21nov2016:13:53:24'dt;
output;
run;
proc sql;
create table want as
select distinct put(datepart(expire_date),monyy7.) as exp_month
from have;
quit;
When you use format, like MONYY7., you dont change the value of the date variable,
then if there are two different dates, you'll get them as distinct.
As to your isuue, there are more than one solution.
I shall use your code with a change:
PROC SQL;
CREATE TABLE WORK.QUERY AS
SELECT DISTINCT
put(datepart(t1.EXPIRE_DATE,9),MONYY7.) as expire_date,
t1.CONT_ID
FROM CONTRACT t1
GROUP BY t1.EXPIRE_DATE;
QUIT;
I might have misunderstood you, but I tried this and it gives me same results:
SELECT DISTINCT put(datepart(t1.EXPIRE_DATE, 9), MONYY7.) as YEAR_MONTH,
count(t1.CONT_ID)
FROM CIS2.CONTRACTS t1
AFAIK, datepart() accepts only one argument. Maybe the ,9 is causing glitches. Try without.
BTW, is CIS2 a SAS library or one in a DBMS?
Still another method would be to "normalize" the datepart() value with the intnx function:
proc sql;
create table want as
select distinct intnx('month',datepart(expire_date),0,'begin') format=monyy7. as exp_month
from have;
quit;
@pkonopnicki wrote:
KurtBremser when I remove '9' or use your code starting with intnx it still gives me the same result : /
If you still use
group by expire_date;
that is the culprit.
I adapted my code
proc sql;
create table want as
select distinct put(datepart(expire_date),monyy7.) as exp_month, count(ID)
from have
group by calculated exp_month
;
quit;
I love you KurtBremser aka SAS Destroyer 😘
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.