06-21-2016 11:40 AM
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
06-21-2016 12:08 PM
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.
06-22-2016 05:09 AM
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.
06-22-2016 06:28 AM
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 -
Need further help from the community? Please ask a new question.