Question on Proc SQL procedure with a case function

[machete]

Hello,

any ideas why the "as" is not read by SAS and it cannot create the variable I am trying to? Any ideas how to ammend the code are welcome:

I am trying to use a similar as data step with an if - then command.

proc sql;

select *,

case when venues2 in (3) then count(distinct bms_broker) as MBPs

else MBPs=0

end as jpeurofinal16

from neo.jpeurofinal15

group by cptyname;

Thanks!!!

Best

Neo

[Reeza]

Case Statement has the wrong syntax:

CASE <case-operand>

WHEN when-condition THEN result-expression

<...WHEN when-condition THEN result-expression>

<ELSE result-expression>

END

I'm not too sure what you're trying to do here as you have the end as well as both conditions references MPBs

This would give you the MPBs variable.

case when venues2 in (3) then count(distinct bms_broker)

else 0

end as MPBs

[PGStats]

Proper syntax, if I guessed what you meant correctly :

proc sql;

create table jpeurofinal16 as

select *,

case when venues2 in (3) then count(distinct bms_broker)

else 0

end as MBPs

from neo.jpeurofinal15

group by cptyname;

quit;

PG

[machete]

Hi,

The code you provided works but does not deliver the result I expected - i thought this should be the solution This code creates a table of the observations where venues2=3 but drops all the the rest, i want to keep the rest as MBPs=0.

I am actually trying to do something simple:

I want to create a new variable MBPs in my dataset which is equal to: if venues2=3 to the count by cptyname of the distinct bms_brokers, for the rest where venues2 ne 3 MBPs = 0

Any ideas?

Thanks

Neo

[Tom]

Code the PG posted should work.  Do you have some sample data where it does not?

data have ;

  input cptyname $ venues2 bms_broker ;

cards;

A 1 1

A 2 2

A 3 3

B 1 1

B 3 2

run;

proc sql;

create table want as

  select *

       , case when venues2 in (3) then count(distinct bms_broker)

              else 0

         end as MBPS

  from have

  group by cptyname

;

quit;

data _null_;

  set want ;

  put (_all_) (:);

run;

A 3 3 3

A 1 1 0

A 2 2 0

B 3 2 2

B 1 1 0

[machete]

Tom,

it took me around 30mins to validate PGStats code because i have around 1 million observations. It does not net the expected result

your code looks fantastic, thats exactly what I wanted to do. IT WORKS perfectly!!!

Thank you so much guys for the timely replies

Best

Neo

[PGStats]

Actually I don't know how that query should be interpreted by proc SQL. It would make more sense to me if it were:

proc sql;

create table jpeurofinal16 as

select *,

case when venues2 in (3) then count(distinct bms_broker)

else 0

end as MBPs

from neo.jpeurofinal15

group by cptyname, venues2;

quit;

PG

[stat_sas]

proc sql;

create table jpeurofinal16 as

select *,

count(distinct(case when venues2 = 3 then bms_broker else 0 end)) as MBPs

from neo.jpeurofinal15

group by cptyname;

quit;

[machete]

this option gives an error, i am not sure why as i am not a very proficient with proc sql:

516  proc sql;

517  create table jpeurofinal17 as

518  select *,

519  count(distinct(case when venues2 = 3 then bms_broker else 0 end)) as MBPs

520  from neo.jpeurofinal15

521  group by cptyname;

ERROR: Result of WHEN clause 2 is not the same data type as the preceding results.

522  quit;

NOTE: The SAS System stopped processing this step because of errors.

[Tom · Accepted solution]

The error message says "WHEN clause 2", which in your case the ELSE clause, has a different type of data than clause 1.

So since 0 is a number then BMS_BROKER must be a character string.  You probably should change the 0 to ' '.

This would then count the distinct values of BMS_BROKER, but ignore any values of BMS_BROKER when the value of VENUES2 is NOT 3.

You can see more clearly what it is doing if you save the CASE() result as another variable.

proc sql;

create table jpeurofinal16 as

  select *

       , case when venues2=3 then bms_broker else ' ' end as fixed_broker

       , count(distinct calculated fixed_broker) as MBPs

  from neo.jpeurofinal15

  group by cptyname

;