topic PROC SQL: group by for n variables from a table without write list of n variables in SAS Programming

PROC SQL: group by for n variables from a table without write list of n variables

MC1985 — Tue, 21 Jun 2016 15:40:37 GMT

Good Morning Everyone,

i'm writing some code for ana ETL process like that:

proc sql noprint;
  create DS_OUT as 
   select t1.var1, t1.var2, ... ,  t1.varN, sum(t2.var1)
       from 
         DS_IN1 as t1
       left join 
         DS_IN2 as t2
       on (t1.var1=t2.var2)
       group by 
           t1.var1, t1.var2, ... ,  t1.varN;
quit;

did someone know a way to make the same query but without writing after the "group by" statement all N variables? a trick?

i try a lot of combination, read a lot of pdf but i don't find a way.

I try this:

proc sql noprint;
  create DS_OUT as 
   select t1.var1, t1.var2, ... ,  t1.varN, sum(t2.var1)
       from 
         DS_IN1 as t1
       left join 
         DS_IN2 as t2
       on (t1.var1=t2.var2)
       group by 
           1-N;
quit;
/*where N is the number of variables of the DS_IN1*/

But I search something like "_ALL_" comand that i write for the "by" statement in proc sort when i want to sort the Dataset for all variables (i try t1._ALL_ but give ERROR: i don't find _ALL_ variables in t1 table of query or something that.)

have a nice day

thank you very much

Re: PROC SQL: group by for n variables from a table without write list of n variables

Reeza — Tue, 21 Jun 2016 16:04:28 GMT

Unfortunately SQL does not support variable

lists. 😞

Re: PROC SQL: group by for n variables from a table without write list of n variables

PGStats — Tue, 21 Jun 2016 16:08:06 GMT

SAS variable lists are not supported by proc SQL, they would be confused with mathematical expressions. Var1-Var9 would be interpreted as the difference between Var1 and Var9. Proc SQL supports many extensions to the SQL standard (dataset options, length and format column properties, macro assignment, many SAS functions, operators such as EQT, etc.), but it cannot go astray of the standard.

Re: PROC SQL: group by for n variables from a table without write list of n variables

LinusH — Wed, 22 Jun 2016 09:09:31 GMT

Even if the actuasl questrion have been aswered, I justy want to a log into the fire...

When doing "regular" programming, you tend to go for as simple coding as possible, for several reasons. Easier maintenance, reducing the no of lines, flexibility etc.

But when it comes to ETL development, I think that many of the coding requirements are different.

For ETL, auditability, traceability and lineage.

By hiding table/datas set and column/variable names in lists or aliases makes those harder to maintain. So what feels awkward in normal programming is an asset in ETL.

If you are doing "serious" ETL you should consider using an ETL tool, driven by metadata (like DI Studio does). Then you define mappings and expressions in the UI, and the awkward SQL code will be generated for you in the background.

Re: PROC SQL: group by for n variables from a table without write list of n variables

Oligolas — Wed, 22 Jun 2016 10:28:45 GMT

Hi Martino, this is the way you are looking for:

PROC SQL noprint;
   SELECT 't1.'||strip(name) into :names separated by ', '
   FROM sashelp.vcolumn
   WHERE libname eq 'SASHELP' and memname eq 'CLASS'
   ;
   CREATE DS_OUT as 
      SELECT &names., sum(t2.var1)
      FROM DS_IN1 as t1
      LEFT JOIN DS_IN2 AS t2
      ON (t1.var1=t2.var2)
      GROUP BY &names.
   ;
QUIT;

Re: PROC SQL: group by for n variables from a table without write list of n variables

MC1985 — Wed, 22 Jun 2016 10:32:25 GMT

thanks is the second way that i used