Sas Proc Sql Returning Duplicate Values Of Group By/order By Variables

I have some fairly simple SQL that should provide 1 row per quarter per asset1. Instead, I get multiple rows per group by. Below is the SQL, a SAS data step, and some of the out

Solution 1:

Realized my comment above is more like an answer.

In SAS SQL, in a query with a group by clause that includes extraneous columns on the select statement (i.e. columns not part of the group by and not derived from an aggregating function), SAS "remerges" the summary statistics back to the original data (with a note to that effect). Most SQLs would just throw an error. Below is an example:

data have;
  input gender $ age score;
  cards;
M 10100
M 20200
F 30300
F 40400
;
run;

proc sql;
  select gender, mean(age) as AvgAge, SCore
    from have
    groupby gender
  ;
quit;

returns:

 gender      AvgAge     score
 F35300F35400
 M               15100
 M               15200

In your code, exposure, co_itd, MM_NINEQTR_LOSS, and MM_LIFE_LOSS are all extraneous columns, causing SAS to remerge.

Whenever remerging occures you will see the following message in SAS log:

NOTE: The query requires remerging summary statistics back with the original data.

See remerging data section in SAS documentation on summary-function for more details