proc sql macro variable with when case..Error?

Reply
Contributor
Posts: 24

proc sql macro variable with when case..Error?

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.

Contributor
Posts: 24

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

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;

Ask a Question
Discussion stats
  • 1 reply
  • 340 views
  • 0 likes
  • 1 in conversation