BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
MC1985
Obsidian | Level 7

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
1 ACCEPTED SOLUTION

Accepted Solutions
5 REPLIES 5
Reeza
Super User

Unfortunately SQL does not support variable

lists. 😞

PGStats
Opal | Level 21

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
LinusH
Tourmaline | Level 20

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
Oligolas
Barite | Level 11

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 -

MC1985
Obsidian | Level 7

thanks is the second way that i used

Martino Crippa

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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