find pairs

[HEB1]

Hi

I want to find distinct pairs of Cusip (this is the ID) by data and investor. that is find a pair of 2 firms with all their institutional investors by date.

this is the code I use:

proc sql;
create table esg.Holdings2 as
select distinct a.NCUSIP as A_cusip, a.Ins_own as A_Ins_own, b.NCUSIP as B_cusip, b.Ins_own as B_Ins_own,
FROM 'esg.Holdings1a' AS A, from 'esg.Holdings1a' AS B
where a.NCUSIP < b.NCUSIP group rdate, mgrno ;
quit; 

this is the mistake:

20! B_Ins_own,
321 FROM 'esg.Holdings1a' AS A, from 'esg.Holdings1a' AS B
322 where a.NCUSIP < b.NCUSIP group rdate, mgrno ;
-----
22
76
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, ',', AS, FORMAT,
FROM, INFORMAT, INTO, LABEL, LEN, LENGTH, TRANSCODE.

ERROR 76-322: Syntax error, statement will be ignored.

I attached an example of current data and what I try to do 

[PeterClemmensen]

Looks like you're missing a BY after GROUP 🙂

Whether your progrem does what it is suppose to from there, I can't tell

[HEB1]

hi
group by is not the problem. the problem is the "where" but I have no
idea why

[s_lassen · Accepted solution]

There are several syntax errors in your SQL:

1. The FROM keyword should come only once in a query, not twice separated by commas
2.  There should not be a comma after the last column before FROM.
3. The GROUP keyword should also be used only once, and it should be followed by a BY

My guess is that what you want to do is to match the records by RDATE and MGRNO, which can be accomplished with a JOIN rather than using GROUP BY:

proc sql;
  create table esg.Holdings2 as
  select distinct 
    a.NCUSIP as A_cusip, 
    a.Ins_own as A_Ins_own, 
    b.NCUSIP as B_cusip, 
    b.Ins_own as B_Ins_own
  from
    'esg.Holdings1a' AS A join 'esg.Holdings1a' AS B
    on a.rdate=b.rdate and a mgrno=b.mgrno and a.NCUSIP < b.NCUSIP;
quit; 

[HEB1]

Thank you SO MUCH!!!!!