DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Contributor
Posts: 34
Accepted Solution

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

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

MC

Martino Crippa

Accepted Solutions
Solution
‎06-21-2016 12:10 PM
Super User
Posts: 17,912

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

Unfortunately SQL does not support variable

lists. 😞

View solution in original post


All Replies
Solution
‎06-21-2016 12:10 PM
Super User
Posts: 17,912

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

Unfortunately SQL does not support variable

lists. 😞

Respected Advisor
Posts: 4,659

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

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. 

PG
Super User
Posts: 5,260

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

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.

 

Data never sleeps
Frequent Contributor
Posts: 104

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

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;
________________________

- Cheers -

Contributor
Posts: 34

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

thanks is the second way that i used

Martino Crippa
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 309 views
  • 1 like
  • 5 in conversation