Is there an alternative to in( ) in Proc SQL?

[Ksharp · Accepted solution]

If you have multiple date for the same mgrno. 

Try this one .

data portfolio;
infile cards expandtabs truncover;
input mgrno	announcement : mmddyy10.;
format announcement mmddyy10.;
cards;
9385	9/16/09
9385	9/16/08
62890	9/14/08
;
run;
data IMPORT2;
 infile cards expandtabs truncover;
input mgrno	fdate  : mmddyy10.	cusip;
format fdate mmddyy10.;
cards;
9385	12/31/07	12345
9385	12/31/08	12345
9385	12/31/09	12345
9385	12/31/07	23456
9385	12/31/08	23456
9385	12/31/07	34567
9385	12/31/07	78911
9385	12/31/08	78912
9385	12/31/06	78912
9385	12/31/06	78912
62890	9/13/08	34567
62890	9/13/07	12345
62890	9/13/11	22345
;
run;
proc sort data=portfolio;
by mgrno descending announcement;
run;
proc sort data=import2;
by mgrno descending fdate;
run;
data temp;
 set portfolio(rename=(announcement=fdate) in=ina) import2(in=inb);
 by mgrno descending fdate;
 retain want;
 if first.mgrno then call missing(want);
 if ina then want=fdate;
 if inb;
format want mmddyy10.;
run;
data want;
 set temp(where=(want is not missing));
 by mgrno want fdate notsorted;
 if first.want then n=0;
 n+first.fdate;
 if n=1;
run;

[Yegen]

Your replies are really, really helpful, @Ksharp. Thanks very much for your explanations.

Also, thanks to @mkeintz for providing help.

[Ksharp]

one more thing need to clarify.

if there are no fdate between two announcement, then will retrun the last announcement.

E.X.

9385	9/15/08
9385	9/16/08

the second one will be ignored . 

[Yegen]

I see. That is good to know. So there will be only one observation (i.e., the last announcement) whenever fdate is not between announcements. 

Thanks. 

[ChrisNZ]

These two small modifications halve the elapse time on my machine:

data tempv/view=tempv;

 set portfolio(rename=(announcement=fdate) in=ina) import2(in=inb);

 by mgrno descending fdate;

 retain want;

 if first.mgrno then call missing(want);

 if ina then want=fdate;

 if inb & want;

 format want mmddyy10.;

run;

data want;

 set tempv;

 by mgrno fdate notsorted;

 if first.mgrno then n=0;

 n+first.fdate;

 if n=1;

run;

[Yegen]

@ChrisNZ thanks for this helpful suggestion! 

[Ksharp]

Yeah. the smallest announcement would be retain.
But of course, you can find these ignored announcement back. Need more code .

[Yegen]

Other than just using a left join with the original dataset, is there another easy way to identify such observations? I am trying to learn any alternative methods, so your suggestion will be very helpful. What I was thinking of is just doing a left join (where on the left side the original data is and the right side the output of the code) and then identifyin the ones that are having missing values after the merge. 

[Ksharp]

That is not good for SQL if you have a big table .