topic changing the date (not the format?) in SAS Procedures

changing the date (not the format?)

pkonopnicki — Tue, 22 Nov 2016 13:30:52 GMT

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;

Re: changing the date (not the format?)

Kurt_Bremser — Tue, 22 Nov 2016 13:37:43 GMT

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;

Re: changing the date (not the format?)

Shmuel — Tue, 22 Nov 2016 13:43:06 GMT

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;

Re: changing the date (not the format?)

pkonopnicki — Tue, 22 Nov 2016 13:48:23 GMT

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

Re: changing the date (not the format?)

Kurt_Bremser — Tue, 22 Nov 2016 13:48:47 GMT

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;

Re: changing the date (not the format?)

Kurt_Bremser — Tue, 22 Nov 2016 13:52:49 GMT

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

Re: changing the date (not the format?)

Kurt_Bremser — Tue, 22 Nov 2016 13:54:45 GMT

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

Re: changing the date (not the format?)

pkonopnicki — Tue, 22 Nov 2016 13:58:43 GMT

KurtBremser when I remove '9' or use your code starting with intnx it still gives me the same result : /

Re: changing the date (not the format?)

pkonopnicki — Tue, 22 Nov 2016 14:01:35 GMT

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.

Re: changing the date (not the format?)

Kurt_Bremser — Tue, 22 Nov 2016 14:04:12 GMT

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

Re: changing the date (not the format?)

pkonopnicki — Tue, 22 Nov 2016 14:11:36 GMT

I love you KurtBremser aka SAS Destroyer 😘