Dear all,
I'd like to find a group of data which has same docdb_family_id and different psn_name, by following codes,
PROC SQL;
CREATE table Step1.example2 AS
SELECT *
FROM Pat_ori.Applicationsnew AS ap
LEFT JOIN Pat_ori.Personapplication as pe on ap.appln_id = pe.appln_id
LEFT JOIN Pat_ori.Companies as co on pe.person_id = co.person_id
WHERE docdb_family_id = docdb_family_id
AND psn_name eq psn_name
GROUP BY docdb_family_id
ORDER BY ap.appln_id
;
QUIT;
but the result shows that
13 PROC SQL;
14 CREATE table Step1.example2 AS
15 SELECT *
16 FROM Pat_ori.Applicationsnew AS ap
17 LEFT JOIN Pat_ori.Personapplication as pe on ap.appln_id = pe.appln_id
18 LEFT JOIN Pat_ori.Companies as co on pe.person_id = co.person_id
19 WHERE docdb_family_id = docdb_family_id
20 AND psn_name eq psn_name
21 GROUP BY docdb_family_id
22 ORDER BY ap.appln_id
23 ;
NOTE: A GROUP BY clause has been discarded because neither the SELECT clause nor the optional HAVING
clause of the associated table-expression referenced a summary function.
WARNING: Variable appln_id already exists on file STEP1.EXAMPLE2.
WARNING: Variable person_id already exists on file STEP1.EXAMPLE2.
ERROR: Sort execution failure.
24 QUIT;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 2:03:55.52
cpu time 20:32.36
Could you please give me suggestion ?
thanks in advance.
Why are you selecting everything (*) from all input tables? This is why you get the warnings. You should know what variable you want in the output dataset, select them.
Next you are "grouping" the data by one variable, and "sorting" the data by another. This is a conflict and why you get the error.
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.