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
Unfortunately SQL does not support variable
lists. 😞
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.
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.
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 -
thanks is the second way that i used
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: