topic Re: How to delete obs for a month in SAS Programming

How to delete obs for a month

aminkarimid — Sat, 14 Oct 2017 19:50:32 GMT

Hello everybody;

Please, consider the following sample:

name	date	value
a	2001.01.01	2
a	2001.01.02	3
a	2001.02.01	1
a	2001.03.05	4
b	2001.01.04	1
b	2001.01.15	4
b	2001.01.21	2
b	2001.01.22	3
b	2001.03.04	2
b	2001.03.07	5
b	2001.04.01	4
c	2001.02.05	2
c	2001.02.10	1
c	2001.02.11	2
c	2001.02.17	2
c	2001.03.10	1
c	2001.04.03	2
c	2001.05.09	3

Now, I want to remove observations of first month for each name variable.

So, the final table is:

name	date	value
a	2001.02.01	1
a	2001.03.05	4
b	2001.03.04	2
b	2001.03.07	5
b	2001.04.01	4
c	2001.03.10	1
c	2001.04.03	2
c	2001.05.09	3

How can I do this?

Thanks in advance.

Re: How to delete obs for a month

SuzanneDorinski — Sat, 14 Oct 2017 21:09:31 GMT

data have;
  length name $ 1 date $ 10;
  input name date value;
  datalines;
a 2001.01.01 2
a 2001.01.02 3
a 2001.02.01 1
a 2001.03.05 4
b 2001.01.04 1
b 2001.01.15 4
b 2001.01.21 2
b 2001.01.22 3
b 2001.03.04 2
b 2001.03.07 5
b 2001.04.01 4
c 2001.02.05 2
c 2001.02.10 1
c 2001.02.11 2
c 2001.02.17 2
c 2001.03.10 1
c 2001.04.03 2
c 2001.05.09 3
  ;
run;

proc print data=have;
  title 'This is have data set';
run;

data have_with_month;
  set have;
  month=substr(date,6,2);
run;

proc sort data=have_with_month;
  by name month;
run;

data first_month_for_name;
  set have_with_month(keep=name month);
  by name month;
  if first.name;
run;

data want(drop=month);
  merge have_with_month(in=h)
        first_month_for_name(in=f);
  by name month;
  if h and not f;
run;

proc print data=want;
  title 'This is want data set';
run;

Re: How to delete obs for a month

Astounding — Sun, 15 Oct 2017 01:44:05 GMT

There are a few loose ends here.

Is your date variable a character string, or is it a true SAS date?
Do your observations ever span more than one year for a single name?

Since you have dots in the middle of your dates, I'll assume that they are character. In that case:

proc sort data=have;

by name date;

run;

data want;

set have;

by name;

length test_ym $ 7;

if first.name then test_ym = date;

retain test_ym;

if date =: test_ym then delete;

drop test_ym;

run;

Re: How to delete obs for a month

Patrick — Sun, 15 Oct 2017 02:35:10 GMT

@aminkarimid

As long as your date variable is numeric containing a SAS Date value, code like below should do:

data have;
  infile datalines dlm=' ' truncover;
  input name $1. date:yymmdd10. value;
  format date date9.;
  datalines;
a 2001.01.01 2
a 2001.01.02 3
a 2001.02.01 1
a 2001.03.05 4
b 2001.01.04 1
b 2001.01.15 4
b 2001.01.21 2
b 2001.01.22 3
b 2001.03.04 2
b 2001.03.07 5
b 2001.04.01 4
c 2001.02.05 2
c 2001.02.10 1
c 2001.02.11 2
c 2001.02.17 2
c 2001.03.10 1
c 2001.04.03 2
c 2001.05.09 3
;
run;

data want(drop=_:);
  set have;
  by name date;
  retain _r_date;
  if first.name then _r_date=date;
  if intck('month',_r_date,date)>0;
run;

Re: How to delete obs for a month

aminkarimid — Sun, 15 Oct 2017 08:04:52 GMT

Hello @Astounding
Q1: Answer: The date is a numeric variable.
Q2: Answer: Yes.
Thanks.

Re: How to delete obs for a month

Ksharp — Sun, 15 Oct 2017 09:47:16 GMT

data have;
  infile datalines dlm=' ' truncover;
  input name $1. date:yymmdd10. value;
  month=month(date);
  format date date9.;
  datalines;
a 2001.01.01 2
a 2001.01.02 3
a 2001.02.01 1
a 2001.03.05 4
b 2001.01.04 1
b 2001.01.15 4
b 2001.01.21 2
b 2001.01.22 3
b 2001.03.04 2
b 2001.03.07 5
b 2001.04.01 4
c 2001.02.05 2
c 2001.02.10 1
c 2001.02.11 2
c 2001.02.17 2
c 2001.03.10 1
c 2001.04.03 2
c 2001.05.09 3
;
run;

data want(drop=group);
  set have;
  by name month;
  if first.name then group=0;
  group+first.month;
  if group ne 1;
run;

Re: How to delete obs for a month

Astounding — Sun, 15 Oct 2017 13:40:03 GMT

Based on those answers, I would go for @Patrick's solution. Off the top of my head, I would want to test what happens with INTCK when the starting point is the first day of the month. But I suspect that Patrick already did that. My solution is fine for a character date variable, while Patrick's is good for a numeric date variable.

Re: How to delete obs for a month

aminkarimid — Mon, 16 Oct 2017 08:20:39 GMT

Thanks @SuzanneDorinski
Your program is awesome;
But, I made a mistake to explain my question. My observations span more than one year for a single name.