topic Re: find pairs in SAS Programming

find pairs

HEB1 — Mon, 25 Oct 2021 08:34:49 GMT

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

Re: find pairs

PeterClemmensen — Mon, 25 Oct 2021 08:52:25 GMT

Looks like you're missing a BY after GROUP 🙂

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

Re: find pairs

HEB1 — Mon, 25 Oct 2021 08:55:26 GMT

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

Re: find pairs

s_lassen — Mon, 25 Oct 2021 11:57:48 GMT

There are several syntax errors in your SQL:

The FROM keyword should come only once in a query, not twice separated by commas
There should not be a comma after the last column before FROM.
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;

Re: find pairs

HEB1 — Thu, 28 Oct 2021 17:21:17 GMT

Thank you SO MUCH!!!!!