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.
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;
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;
There are a few loose ends here.
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;
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.
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;
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;
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.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.