How to delete obs for a month

Accepted Solution Solved
Reply
Regular Contributor
Posts: 165
Accepted Solution

How to delete obs for a month

Hello everybody;

Please, consider the following sample:

namedatevalue
a2001.01.012
a2001.01.023
a2001.02.011
a2001.03.054
b2001.01.041
b2001.01.154
b2001.01.212
b2001.01.223
b2001.03.042
b2001.03.075
b2001.04.014
c2001.02.052
c2001.02.101
c2001.02.112
c2001.02.172
c2001.03.101
c2001.04.032
c2001.05.093

 

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

So, the final table is:

namedatevalue
a2001.02.011
a2001.03.054
b2001.03.042
b2001.03.075
b2001.04.014
c2001.03.101
c2001.04.032
c2001.05.093

 

How can I do this?

 

Thanks in advance.


Accepted Solutions
Solution
‎10-16-2017 04:24 AM
Respected Advisor
Posts: 4,278

Re: How to delete obs for a month

Posted in reply to aminkarimid

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

View solution in original post


All Replies
Frequent Contributor
Posts: 93

Re: How to delete obs for a month

Posted in reply to aminkarimid
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;
Regular Contributor
Posts: 165

Re: How to delete obs for a month

Posted in reply to SuzanneDorinski
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.
Super User
Posts: 5,987

Re: How to delete obs for a month

Posted in reply to aminkarimid

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;

Regular Contributor
Posts: 165

Re: How to delete obs for a month

Posted in reply to Astounding
Hello @Astounding
Q1: Answer: The date is a numeric variable.
Q2: Answer: Yes.
Thanks.
Super User
Posts: 5,987

Re: How to delete obs for a month

Posted in reply to aminkarimid

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.

 

Solution
‎10-16-2017 04:24 AM
Respected Advisor
Posts: 4,278

Re: How to delete obs for a month

Posted in reply to aminkarimid

@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; 
Super User
Posts: 10,315

Re: How to delete obs for a month

Posted in reply to aminkarimid

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; 

☑ This topic is solved.

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

Discussion stats
  • 7 replies
  • 156 views
  • 0 likes
  • 5 in conversation