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
There are several syntax errors in your SQL:
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;
Looks like you're missing a BY after GROUP 🙂
Whether your progrem does what it is suppose to from there, I can't tell
There are several syntax errors in your SQL:
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.