Since I added a case loop in my proc sql, the grouping does not work anymore

sfmeier — Tue, 17 Oct 2017 15:18:43 GMT

PROC SQL;
/* Entwicklung */
   CREATE TABLE TEMP AS
   SELECT case   when t1.KONTO_ID in (&Nostrokonten)
                   then 0 /* Nostrokonten */
               else t2.MLR_ProduktgruppeID
               end as MLR_ProduktgruppeID format=mlr_produktgruppe.,
               sum(Bilanzsaldo) as Bilanzsaldo format=commax18.2,
            t1.Stichtag
      FROM S1_KONTO&stichtag t1
       LEFT JOIN DB.MAPPING_GAB2MLR_PRODUKTGRUPPE t2
       ON t1.GAB = t2.GAB
      GROUP BY MLR_ProduktgruppeID,
               Stichtag;
quit;

Re: Since I added a case loop in my proc sql, the grouping does not work anymore

Jeremy_Browne — Tue, 17 Oct 2017 15:52:06 GMT

Either use an alias for the case statement that is not the same as the variable (t2.)MLR_ProduktgruppeID: i.e. ..AS MLR_ProduktgruppeID_2 and use the alias in the GROUP BY. Ambiguity between VAR and alias may be the issue AND/OR if that doesn't fix, also put the CASE statement longhand in the GROUP BY (without the AS...)

topic Since I added a case loop in my proc sql, the grouping does not work anymore in SAS Procedures

Since I added a case loop in my proc sql, the grouping does not work anymore

Re: Since I added a case loop in my proc sql, the grouping does not work anymore