topic order or operations -proc sql in SAS Programming

order or operations -proc sql

Ronein — Sat, 04 Mar 2023 16:18:02 GMT

Hello

I know that in proc sql there are rules of order of operations:

From---Where-----Select

So in this case first go to data set "Have"

Then perform row filter (obs=1) so it take only first row

Then it perform the select activities.

My question:

Since we asked to take only one row,

then how the operations in select that working on all observations will work?

select 'TOTAL' as Category,
(select count(*) as nr_customers from have),
1 as PCT_Customers,
(select sum(wealth) as Sum_Wealth from have),
1 as Pct_Wealth
from have(obs=1);
quit;

Re: order or operations -proc sql

Kurt_Bremser — Sat, 04 Mar 2023 16:25:08 GMT

Because the summations are done in the sub-selects, where all observations are read.

Re: order or operations -proc sql

ballardw — Sat, 04 Mar 2023 16:30:38 GMT

How about describing what you are attempting to do with example data?

Or try removing the (OBS=1) and see the result. In this case I am pretty sure that the given code will yield NN number of identical records where NN is the number of observations in the data set.

SQL does not have any standard order of using records so I am not sure what you are asking about in this case.

Your code

(select count(*) as nr_customers from have)

Will not create a variable nr_customers but a generated name like _TEMA002. If you want the result to be named nr_customers the syntax is

(select count(*) from have) as nr_customers