Using a Macro Program

Question

I am trying to use a Macro program to make a record sorted by TotalGPA of the top ten percent GPA's which turns out to be the 24 highest GPAs, that have more than 60 credit hours but less than 130 credit hours. Here is my code below

%MACRO TopTen(outputtable, less, more);
PROC SQL;
select round(count(ID)/10) into :Data from OverallGPA;
quit;

%PUT &Data;

PROC SQL outobs=&Data.;
    Create table &outputtable. as select * 
    from OverallGPA,OverallCreditHoursEarned
    where &less.<OverallCreditHoursEarned<&more.
    order by ID
;
quit;

%MEND;

%TopTen(Report3, 60, 130);
/* creates report of number of values in top ten percent */
PROC REPORT data=Report3;
run;

This Proc Report Currently is printing out columns of the same ID, TotalGPA, but different OverallCreditHoursEarned. Any ideas to make this work? I used Proc Rank which I think works but not with a Macro. The Proc Rank code is below.

   PROC RANK data=OverallGPA out=Report3Alt(where=(TopTenPercent<=24)) 
   descending ties=Low;
   var TotalGPA;
   ranks TopTenPercent;
   run;

Answer 1

Your SQL join is not doing what you want. This:

from OverallGPA, OverallCreditHoursEarned
where &less. <  OverallCreditHoursEarned < &more

This doesn't tell SAS (or SQL) what to join those two tables on, so it's joining every record in each table to each record in the other table. If you have 100 records in 1 table and 100 records in the other table, it will make 10,000 records in the output table. This is not what you want.

First, I would highly recommend simply using PROC RANK as that is what its job is. You're paying for SAS, use SAS when it makes sense. Put it in the macro just where you have the main PROC SQL join. Or, use the GROUPS option in PROC RANK which will group your records into deciles (or whatever), and filter the output datset WHERE GROUP=0 or whatever is correct for your needs. This might work:

PROC RANK data=OverallGPA out=Report3Alt(where=(TopTenPercent=0)) groups=10 
   descending ties=Low;
   var TotalGPA;
   ranks TopTenPercent;
   run;

But, if you want to use SQL, make your join correctly - either use an explicit join with an on statement, or add an equality using the ID variables to your where statement. I prefer explicit joins with on statements, so something like

from OverallGPA inner join OverallCreditHours 
on OverallGPA.ID = OverallCreditHours.ID
where OverallCreditHours.OverallCreditHours between &less and &more

Or equivalent. You might want a left or a right join, it's not clear to me, but inner seems most likely.