BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
pkonopnicki
Obsidian | Level 7

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

@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;

View solution in original post

10 REPLIES 10
Kurt_Bremser
Super User

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;

 

Shmuel
Garnet | Level 18

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;

pkonopnicki
Obsidian | Level 7

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

 

Capture.PNG

Kurt_Bremser
Super User

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
Obsidian | Level 7
KurtBremser when I remove '9' or use your code starting with intnx it still gives me the same result : /
pkonopnicki
Obsidian | Level 7
The one solution would be doing this
put(datepart(EXPIRE_DATE, 9), MONYY7.)
without count(cont_id), just regular cont_id and then in next query do the count.
Kurt_Bremser
Super User

@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;
pkonopnicki
Obsidian | Level 7

I love you KurtBremser aka SAS Destroyer 😘

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 1414 views
  • 1 like
  • 3 in conversation