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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.