Help using Base SAS procedures

changing the date (not the format?)

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

changing the date (not the format?)

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;


Accepted Solutions
Solution
‎11-22-2016 09:10 AM
Super User
Posts: 7,821

Re: changing the date (not the format?)

Posted in reply to pkonopnicki

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Super User
Posts: 7,821

Re: changing the date (not the format?)

Posted in reply to pkonopnicki

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;

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Trusted Advisor
Posts: 1,572

Re: changing the date (not the format?)

Posted in reply to pkonopnicki

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;

Occasional Contributor
Posts: 12

Re: changing the date (not the format?)

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

Super User
Posts: 7,821

Re: changing the date (not the format?)

Posted in reply to pkonopnicki

AFAIK, datepart() accepts only one argument. Maybe the ,9 is causing glitches. Try without.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 7,821

Re: changing the date (not the format?)

Posted in reply to pkonopnicki

BTW, is CIS2 a SAS library or one in a DBMS?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 7,821

Re: changing the date (not the format?)

Posted in reply to pkonopnicki

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 12

Re: changing the date (not the format?)

Posted in reply to KurtBremser
KurtBremser when I remove '9' or use your code starting with intnx it still gives me the same result : /
Occasional Contributor
Posts: 12

Re: changing the date (not the format?)

Posted in reply to pkonopnicki
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.
Solution
‎11-22-2016 09:10 AM
Super User
Posts: 7,821

Re: changing the date (not the format?)

Posted in reply to pkonopnicki

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 12

Re: changing the date (not the format?)

Posted in reply to KurtBremser

I love you KurtBremser aka SAS Destroyer :*

☑ This topic is solved.

Need further help from the community? Please ask a new question.

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