Can I use grouping variable in the select statement in sql?

Reply
Super Contributor
Posts: 338

Can I use grouping variable in the select statement in sql?

Hi SAS Community,

Could any one help me on this sample sql code.

In the follwing sql code, the variable I am using for grouping is ‘acct_num’.

Question:

i). I am extracting the same variable at the top (after ‘select” statement). Can I do like this?

ii). And there is no any max or min prefix in front of it. Is this ok?

proc sql;

  create table temp as

  select

acct_num                                                                      ,

max (Date)                                as    Date  ,

max (type)                                as    type              ,

min   (var_37)  /*Days delinquent*/       as    var_37                 

from b.temp

group by acct_num

;

quit;

Thank you

Mirisage

PROC Star
Posts: 7,471

Re: Can I use grouping variable in the select statement in sql?

Your code appears to run correctly, thus I would say your answer is yes!

Contributor SGB
Contributor
Posts: 41

Re: Can I use grouping variable in the select statement in sql?

Hi Mirisage

Question:

i). I am extracting the same variable at the top (after ‘select” statement). Can I do like this?

You can extract the same variable and use it as Group variable as well.

 

The GROUP BY clause can be used to summarize or aggregate data.

ii). And there is no any max or min prefix in front of it. Is this ok?

Its fine.

Your code will group the observations by account number and gives the max date, max type and min var_37.
For example

Account     Date     type  var_37

Num

1          JUNE8     1     9

1          JUNE9     2     8

1          JUNE10    3     7

1          JUNE11    4     6

It will produce

1     june11    4     6

Hope this helps.

BG

Super Contributor
Posts: 282

Re: Can I use grouping variable in the select statement in sql?

Hi,

Further, if you want to check the syntax without executing the code then you can use the noexec option, e.g.:

proc sql noexec;

  <your sql statement>

  ;

quit;

or the validate statement before a select statement, e.g.:

proc sql;

  validate

  <your select statement>

  ;

quit;

HTH.

Regards,

Amir.

Ask a Question
Discussion stats
  • 3 replies
  • 208 views
  • 0 likes
  • 4 in conversation