BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
lixuan
Obsidian | Level 7

Hi, I ask the question 2 days before , but there is a new problem . The data just like following . I wanna  pick the data from dataset 'have_event'  if it's date is in 12 months before the date of dataset 'have_base'.  I also show the result I want and the code i tried wihch didn't work.

data have_base;
input company$ _date:$6. profit;
date = input(_date !! '01',yymmdd8.);
format date yymmn6.;
drop _date;
datalines;
a 199701 5
a 199606 9
b 201404 6
f 200004 78
;
run;

data have_event;
input company$ _date:$6. EPS;
date = input(_date !! '01',yymmdd8.);
format date yymmn6.;
drop _date;
datalines;
a 199608 5
a 199706 30
a 199605 4
a 199712 9
a 199806 10
c 201404 6
k 200004 78
n 198607 56
a 198504 3
b 201304 3
b 201305 6
b 201306 8
b 201504 80
b 201603 5
;
run;
proc sort data=have_event;
by company;
run;

data want;
merge
have_base (in=b rename=(date=date_b))
have_event (in=e)
;
by company;
if b and e;
if date >= intnx('month',date_b, -12);
/*if 0>=intck('month',date,date_b) >=-12;*/
drop profit date_b;
run;

proc print data=want noobs;
run;

 The result I want should be :

a 199608 5
a 199605 4
a 199605 4
b 201304 3
b 201305 6
b 201306 8

 

for example , as for  a, The date in dataset 'have_base'  is  '199701' and '199606', so the date in 'have_event' dataset  should be (199601 to 199612) and in(199506 to 199605) and I get the right EPS.

Thanks&regards

1 ACCEPTED SOLUTION

Accepted Solutions
Jagadishkatam
Amethyst | Level 16

I did not change anything except the format monyy6.

 

data have_base;
input company$ _date:$6. profit;
date = input(_date !! '01',yymmdd8.);
format date  monyy6.;
drop _date;
datalines;
a 199701 5
a 199606 9
b 201404 6
f 200004 78
;
run;

data have_event;
input company$ _date:$6. EPS;
date = input(_date !! '01',yymmdd8.);
format date monyy6.;
drop _date;
datalines;
a 199608 5
a 199706 30
a 199605 4
a 199712 9
a 199806 10
c 201404 6
k 200004 78
n 198607 56
a 198504 3
b 201304 3
b 201305 6
b 201306 8
b 201504 80
b 201603 5
;
run;
proc sort data=have_event;
by company;
run;


proc sql;
create table test as select a.*,b.eps,b.date as date_event format=monyy6. from have_base as a, have_event as b where intnx('month',a.date, -12)<=b.date<=a.date
and a.company=b.company;
quit;
Thanks,
Jag

View solution in original post

13 REPLIES 13
Jagadishkatam
Amethyst | Level 16

proc sql;
create table test as select a.*,b.eps,b.date as date_event format=monyy6. from have_base as a, have_event as b where intnx('month',a.date, -12)<=b.date<=a.date;
quit;
Thanks,
Jag
lixuan
Obsidian | Level 7

Thank you, I changed your code and ran it .

proc sql;
create table test as select a.*,b.eps,b.date as date_event format=monyy6.
from have_base as a, 
	 have_event as b
where intnx('month',a.date, -12)<=b.date<=a.date
	 &a.company=b.company;
quit;

The result is follwing ,but actually it skips one observation:a 199605 4(EPS). Because the second record in 'have_base' has a date 199606, and I also wanna get the record in 'have_event' which is in 12 months before 199606.

company profit date EPS date_event
a51997015AUG96
a51997014MAY96
b62014043APR13
b62014046MAY13
b62014048JUN13
Jagadishkatam
Amethyst | Level 16

 

I followed the same getting your expected output. Could you please check again

 

proc sql;
create table test as select a.*,b.eps,b.date as date_event format=monyy6. from have_base as a, have_event as b where intnx('month',a.date, -12)<=b.date<=a.date
and a.company=b.company;
quit;

output:

 

image.png

Thanks,
Jag
lixuan
Obsidian | Level 7

How could it happen? I copy your codes without change, but my result is 

SAS Output


company profit date EPS date_event
a51997015AUG96
a51997014MAY96
b62014043APR13
b62014046MAY13
b62014048JUN13
lixuan
Obsidian | Level 7

Did you chang my input before? Could you post all the codes? thank u

Jagadishkatam
Amethyst | Level 16

I did not change anything except the format monyy6.

 

data have_base;
input company$ _date:$6. profit;
date = input(_date !! '01',yymmdd8.);
format date  monyy6.;
drop _date;
datalines;
a 199701 5
a 199606 9
b 201404 6
f 200004 78
;
run;

data have_event;
input company$ _date:$6. EPS;
date = input(_date !! '01',yymmdd8.);
format date monyy6.;
drop _date;
datalines;
a 199608 5
a 199706 30
a 199605 4
a 199712 9
a 199806 10
c 201404 6
k 200004 78
n 198607 56
a 198504 3
b 201304 3
b 201305 6
b 201306 8
b 201504 80
b 201603 5
;
run;
proc sort data=have_event;
by company;
run;


proc sql;
create table test as select a.*,b.eps,b.date as date_event format=monyy6. from have_base as a, have_event as b where intnx('month',a.date, -12)<=b.date<=a.date
and a.company=b.company;
quit;
Thanks,
Jag
lixuan
Obsidian | Level 7

Yeah, I get the same result with yours, but I don't know why. In my opinion, format should not influence the result. Do you know the reason?


company profit date EPS date_event
a5JAN975AUG96
a5JAN974MAY96
a9JUN964MAY96
b6APR143APR13
b6APR146MAY13
b6APR148JUN13
Jagadishkatam
Amethyst | Level 16

I agree with you the format should not influence, i ran the code again with the earlier format yymmn6. that you used and i get the same  result

 

data have_base;
input company$ _date:$6. profit;
date = input(_date !! '01',yymmdd8.);

format date yymmn6.;
drop _date;
datalines;
a 199701 5
a 199606 9
b 201404 6
f 200004 78
;
run;

data have_event;
input company$ _date:$6. EPS;
date = input(_date !! '01',yymmdd8.);
format date yymmn6.;
drop _date;
datalines;
a 199608 5
a 199706 30
a 199605 4
a 199712 9
a 199806 10
c 201404 6
k 200004 78
n 198607 56
a 198504 3
b 201304 3
b 201305 6
b 201306 8
b 201504 80
b 201603 5
;
run;
proc sort data=have_event;
by company;
run;


proc sql;
create table test as select a.*,b.eps,b.date as date_event  from have_base as a, have_event as b where intnx('month',a.date, -12)<=b.date<=a.date
and a.company=b.company;
quit;
Thanks,
Jag
lixuan
Obsidian | Level 7

OK, thank you very much. I will try it again. You do me a good favor!

lixuan
Obsidian | Level 7

 Hi ,Jag. Whatever I try, I can't get the wrong result. I don't know, maybe it's just the software's mistake. thank you 


Tom
Super User Tom
Super User

You have two base dates for company 'a'.  Which one do you want to use?

lixuan
Obsidian | Level 7

I want use both. For example, in 'have_base' dataset, somthing happened in the date, and in 'have_event' dataset, the character has benn recorded. I want to konw the change of chatacter before or after the date of 'have_base" dataset. Thanks

lixuan
Obsidian | Level 7

Maybe it's a impossible mission. I explain  more detailly. as you see, a in 'have_base' have two records.

First,  date of a  in dataset 'have_event' should be compared  with the first record of a in 'have_base'. If date of a in 'have_event'  is in 12 months before the date of first record in 'have_base', it will be picked out, and I get the first two records of result which contains two a .

Second, I prefer doing the same thing with the second record of 'have_base'. That is treating  the date of second record a in 'have_base' as a benchmark to find the right record in 'have_event'. As a result, I get the third record a in my result.

My english is poor, and I wonder if I express my idea clearly . I hope to get your help, thank you.

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
  • 13 replies
  • 1300 views
  • 0 likes
  • 3 in conversation