BookmarkSubscribeRSS Feed

I would like to see query builders have an option to perform a SELECT * statement.  For instance, I don't believe the following is possible using a query builder:

 

PROC SQL;

  CREATE TABLE WORK.TMP AS

  SELECT t1.*, MAX(t1.Age) AS Max_Age

  FROM SASHELP.CLASS t1

  GROUP BY t1.Sex;

QUIT;

 

Currently when you right click on a table in the left hand pane and choose "Select All Columns" it inserts each column separately and ends up building the following code:

 

PROC SQL;

   CREATE TABLE WORK.TMP AS

   SELECT t1.Name,

          t1.Sex,

          t1.Age,

          t1.Height,

          t1.Weight,

          /* Max_Age */

            (MAX(t1.Age)) AS Max_Age

      FROM SASHELP.CLASS t1

      GROUP BY t1.Sex;

QUIT;

 

I often have very long process flows with several query builders that are bringing all columns forward with them.  If I add a new column to a table at the beginning of the flow, I then have to open each subsequent query builder and add the column.  Having a special "select all" row in the select data tab of the query builder would alleviate this issue.

8 Comments
Kurt_Bremser
Super User

I'd even say the Query Builder in EG should detect the fact that a user moved the whole table over into the columns window by itself and only go to the individual listing of columns when an additional change is made (like applying a format or so).

RW9
Diamond | Level 26
Diamond | Level 26

TBH the select * wildcard syntax is quite frowned upon from what I have seen.  Its a bit like saying you don't know what is in the table, or don't want to be bothered.  I know we all do it.  With most commerical SQL developments you shouldn't see this, and SQL builders do the same as you mention above, i.e. select all means put the variables in the step.  Its better to explicitly take certain data you want.  I don't actually see why the second piece of code is that difficult to work with?  At least if I look at that code, without knowing what you have done, I can see clearly that certain variables are taken.  Conversely, your suggestion to alleviate you having to update each select when you add another variable, also introduces the possibility of carrying forward errors.

KatS_SAS
SAS Employee

Thank you for the suggestion.  I have entered a suggestion into our system for Product Mangement.

ballardw
Super User

I know I'm not much of an SQL programmer but this logic seems to say that if I'm working in SAS that every datastep SET statement I use should have an explicit KEEP statement as well as a data set option KEEP for every procedure I run.

Would it be a wrong if there was a button/tool that inserted all of the variables into a select clause?

RW9
Diamond | Level 26
Diamond | Level 26

There are reasons for it.  Anyways, the OP has a way to insert all the columns from his right click menu, what he is saying is that he doesn't want to update all the text that generates, my point comes from the other way, if you don't explicitly know what should come through then further code may stop working, e.g. first we have a val column, which in a subsequent step is used to sum.  With the first step selecting this variable then it will fall over there an be quite obvious why, if you have select *, it may not be picked up until a long way into further code.  Its a bit of a small thing if the SQL is small, but when there are many steps, merges etc. it can get tricky.

Just to add, I haven't really used the query builder, always looked a bit clunky to me.  Most SQL dev apps treat tables, variables like objects, you can grab variables, link things up quickly and easily that way without having to type much.  Don't know how close QB is to that?

BeverlyBrown
Community Manager
Status changed to: Under Consideration
 
AliceBrunel
Calcite | Level 5

then for the subsequent query all newly created variables would be selected by default and user won't have to go thru all query to add the new varaibles

ChrisHemedinger
Community Manager

@AliceBrunel - we merged your similar idea in with this one, which has been marked Under Consideration