Help using Base SAS procedures

PROC SQL automatic rank variable?

Reply
Frequent Contributor
Posts: 77

PROC SQL automatic rank variable?

My SAS code:

proc sql;
create table a1 as
select region,
sum(sales) as tot_sales
from sashelp.shoes
group by region
order by tot_sales desc
; quit;

data a2;
set a1;
rank = _n_;
run;

I want to know if it is possible to eliminate the second data step and let the first proc sql create rank variable.
Contributor sss
Contributor
Posts: 65

Re: PROC SQL automatic rank variable?

Hi
Gud one i dont know how to do in Proc sql;

Base sas Code goes here...


DATA SHOES(KEEP =REGION TOT_SALES);

SET SHOES;
BY REGION;
IF FIRST.REGION THEN TOT_SALES=0;
TOT_SALES+SALES;
IF LAST.REGION THEN OUTPUT;
RANK=_N_;
RUN;
Super Contributor
Super Contributor
Posts: 365

Re: PROC SQL automatic rank variable?

Hello SSS,

It is more like this:
[pre]
data Shoes (KEEP =REGION TOT_SALES rank);
set sashelp.shoes;
if FIRST.region then do; TOT_SALES=0; Rank+1; end;
TOT_SALES+SALES;
if LAST.region then output;
by region;
run;
[/pre]
Sincerely,
SPR Message was edited by: SPR
Contributor sss
Contributor
Posts: 65

Re: PROC SQL automatic rank variable?

Hi SPR,

it would be more likely

PROC SORT DATA=SASHELP.SHOES OUT=SHOES;
by decending REGION;
RUN;

data Shoes (KEEP =REGION TOT_SALES rank);
set shoes;
if FIRST.region then do; TOT_SALES=0; Rank+1; end;
TOT_SALES+SALES;
if LAST.region then output;
by descending region;
run;
Respected Advisor
Posts: 3,777

Re: PROC SQL automatic rank variable?

I don't know either but PROC SUMMARY will do it.

[pre]
proc sql;
create table a1 as
select region,
sum(sales) as tot_sales
from sashelp.shoes
group by region
order by tot_sales desc
; quit;
data a2;
set a1;
rank = _n_;
run;

proc print;
run;



proc summary data=sashelp.shoes nway;
class region / order=freq descending;
freq sales;
output out=ranks / levels;
run;
proc print;
run;
proc compare base=a2 compare=ranks;
var region tot_sales rank;
with region _freq_ _level_;
run;
[/pre]
PROC Star
Posts: 7,363

Re: PROC SQL automatic rank variable?

Marc,

If you can use an undocumented function (that comes with the risk that the feature may not exist in future releases), you can definitely do it in a 2nd proc sql step and can probably combine the two. E.g.:
[pre]

proc sql noprint;
create table a1 as
select distinct region,
sum(sales) as tot_sales
from sashelp.shoes
group by region
order by tot_sales desc
;
create table want as
select *,
monotonic() as rank
from a1
order by tot_sales desc
;
quit;
[/pre]
HTH,
Art
Super User
Posts: 5,258

Re: PROC SQL automatic rank variable?

None of the above solution is a single SQL.
Question is, why is that important? Is this you real summarized data huge, and you want to avoid more than one pass of data? In that case, one could use a view approach for the SQL group by step.
And for ranking, why not use a procedure which was created to do - ranking...?
In my eyes, it is the simplest (and supported) code:


proc rank data=work.a1 out=a2 descending;
var tot_sales;
ranks rank_sales;
run;

/Linus Oh, missed the ranks statement...


Message was edited by: Linus H
Data never sleeps
Ask a Question
Discussion stats
  • 6 replies
  • 9456 views
  • 0 likes
  • 6 in conversation