BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
aminkarimid
Lapis Lazuli | Level 10

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

@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

7 REPLIES 7
SuzanneDorinski
Lapis Lazuli | Level 10
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;
aminkarimid
Lapis Lazuli | Level 10
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.
Astounding
PROC Star

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;

aminkarimid
Lapis Lazuli | Level 10
Hello @Astounding
Q1: Answer: The date is a numeric variable.
Q2: Answer: Yes.
Thanks.
Astounding
PROC Star

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.

 

Patrick
Opal | Level 21

@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; 
Ksharp
Super User

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; 

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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