topic Re: PROC SQL automatic rank variable? in SAS Procedures

PROC SQL automatic rank variable?

MarcTC — Thu, 05 May 2011 00:04:31 GMT

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.

Re: PROC SQL automatic rank variable?

sss — Thu, 05 May 2011 14:54:18 GMT

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;

Re: PROC SQL automatic rank variable?

SPR — Thu, 05 May 2011 15:17:45 GMT

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

Re: PROC SQL automatic rank variable?

sss — Thu, 05 May 2011 15:44:26 GMT

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;

Re: PROC SQL automatic rank variable?

data_null__ — Thu, 05 May 2011 16:26:32 GMT

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]

Re: PROC SQL automatic rank variable?

art297 — Thu, 05 May 2011 17:44:29 GMT

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

Re: PROC SQL automatic rank variable?

LinusH — Fri, 06 May 2011 06:56:37 GMT

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