topic Re: find a group of data which has same variable 1 and different variable 2 in SAS Programming

find a group of data which has same variable 1 and different variable 2

France — Fri, 07 Sep 2018 12:46:29 GMT

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.

Re: find a group of data which has same variable 1 and different variable 2

gamotte — Fri, 07 Sep 2018 12:57:20 GMT

Hello,

I think you can get rid of the WHERE clause as filtering by variables equal to themselves won't change the resulting output.

Since you don't provide data, i can't test here but try :

GROUP BY docdb_family_id
HAVING count(*) ne count(DISTINCT psn_name)

Re: find a group of data which has same variable 1 and different variable 2

RW9 — Fri, 07 Sep 2018 12:57:42 GMT

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.