sas sql

Reply
Contributor
Posts: 62

sas sql

i have aa data set 

data t;

name   rating  post;

cards;

a   2   ASE

b   3   ASE

c  4    ITA

d  5   ITA

e  2  VP

f   7  Vp

;run;

 

want to knw the rating of each person out of whole rating and performed the following query


proc sql;
select first_name,(calculated rating/r) from pawan.test,(select sum(rating) as r from pawan.test1);
quit;

 

getting error 


ERROR: It appears that the CALCULATED variable rating was referenced before it was defined.
ERROR: The following columns were not found in the contributing tables: post.
ERROR: The following columns were not found as CALCULATED references in the immediate query: rating.

Respected Advisor
Posts: 4,930

Re: sas sql

I suspect that what you need is not SQL but proc rank:

 

data have;
length name $12 rating 8 post $12;
input name   rating  post;
cards;
a  2  ASE
b  3  ASE
c  4  ITA
d  5  ITA
e  2  VP
f  7  Vp
;

proc rank data=have out=want ties=dense;
var rating;
ranks ratingRank;
run;

proc print data=want noobs; run;

if not, tell us what you expect as a result.

 

PG
Contributor
Posts: 62

Re: sas sql

i want that for "a" total_rating is "2/sum(rating)"
and for "b" is "3/sum(rating)" like so on
Trusted Advisor
Posts: 1,228

Re: sas sql

Proc sql;

Select *,rating/(select sum(rating) from t) as total_rating

From t;

Quit;

Super User
Posts: 19,855

Re: sas sql

SAS SQL can accomplish this fairly easily.

 

 

proc sql;
create table want as
select *, rating/sum(rating) as pct_rating
from have;
quit;
Ask a Question
Discussion stats
  • 4 replies
  • 384 views
  • 0 likes
  • 4 in conversation