topic Re: sas sql in SAS Programming

sas sql

pawandh — Sat, 02 Jan 2016 04:47:01 GMT

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.

Re: sas sql

PGStats — Sat, 02 Jan 2016 05:31:51 GMT

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.

Re: sas sql

pawandh — Sat, 02 Jan 2016 06:37:13 GMT

i want that for "a" total_rating is "2/sum(rating)"
and for "b" is "3/sum(rating)" like so on

Re: sas sql

stat_sas — Sat, 02 Jan 2016 06:56:21 GMT

Proc sql;

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

From t;

Quit;

Re: sas sql

Reeza — Sat, 02 Jan 2016 13:45:04 GMT

SAS SQL can accomplish this fairly easily.

proc sql;
create table want as
select *, rating/sum(rating) as pct_rating
from have;
quit;