proc sql macro variable with when case..Error?

BOBSAS — Fri, 15 May 2015 01:57:56 GMT

Hi,

I tried below but not luck. Getting an error and warning.

I can create a dataset and solve the issue but i want to know how to resolve it without creating dataset.

data have;

input

subjid trtn expdy ;

cards;

101 1 10

102 2 20

103 3 45

104 1 23

105 2 42

106 3 46

107 1 61

109 2 22

110 3 28

111 1 30

112 2 32

113 3 39

;

run;

proc sql print;

select expdy ,trtn

,(case when expdy>=40 then 40 else expdy end) as expd

,sum(calculated expd) as sumexpd

,mean(calculated sumexpd) into :s1-:s3

from have

group by trtn;

quit;

%put &s1 &s2 &s3;

WARNING: INTO clause specifies fewer host variables than columns listed in the SELECT clause.

ERROR: Summary functions nested in this way are not supported.

How to get values 103 114 147 without creating a dataset.

Re: proc sql macro variable with when case..Error?

BOBSAS — Fri, 15 May 2015 02:01:24 GMT

Found it.

proc sql;

select sum (expdy1) into :n1 - :n3

from (select subjid, trtn, expdy, case

when expdy >= 40 then 40

else expdy

end as expdy1 from have)

group by trtn;

quit;