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

My question is about the select statement and its usage. 

Following is the code used to calculate average, i copied it from base sas codes avaiable online to understand function but can't understand why does it have "e. and d.". and what does it mean to say "class e class d",

Also need to understand the calcuation highlighted [in bold].

 

Step 1  : Creating Data set

data class;
set sashelp.class;
obs = _n_;
run;

 

Step 2 : Proc Sql

proc sql;
select avg(weight) as Median
from (select e.weight
from class e, class d
group by e.weight
having sum(case when e.weight = d.weight then 1 else 0 end)
>= abs(sum(sign(e.weight - d.weight))));
quit;

 

 

Thanks !

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisBrooks
Ammonite | Level 13

Firstly e and d are aliases i.e. they are ways of referring to different variables in different data sets. In this particular case e and d both refer to the same data set and what is being produced is a cartesian product i.e. a data set which contains all possible combinations of weights (the table is being joined with itself).

 

The highlighted code is basically a complicated way of finding the median (i.e. the value with the same number of observations which are greater than it and which are less than it) by using the sum of differences. If you're using SAS 9.4 you can simply do this instead

 

proc sql;
	select median(weight)
	from class;
quit;

View solution in original post

3 REPLIES 3
PeterClemmensen
Tourmaline | Level 20

It may contribute to your understanding to know that you could just as well write

 

from class as e, class as d

e and d are simply the names you are using to refference you datasets, so you can refference them by

 

 

select avg(weight) as Median
from (select e.weight
ChrisBrooks
Ammonite | Level 13

Firstly e and d are aliases i.e. they are ways of referring to different variables in different data sets. In this particular case e and d both refer to the same data set and what is being produced is a cartesian product i.e. a data set which contains all possible combinations of weights (the table is being joined with itself).

 

The highlighted code is basically a complicated way of finding the median (i.e. the value with the same number of observations which are greater than it and which are less than it) by using the sum of differences. If you're using SAS 9.4 you can simply do this instead

 

proc sql;
	select median(weight)
	from class;
quit;
JFario09
Calcite | Level 5

Thanks 🙂

It worked when I used SAS 9.4.

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
  • 3 replies
  • 779 views
  • 1 like
  • 3 in conversation