topic Question on Proc SQL procedure with a case function in SAS Procedures

Question on Proc SQL procedure with a case function

machete — Sun, 07 Sep 2014 22:14:43 GMT

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

Re: Question on Proc SQL procedure with a case function

Reeza — Sun, 07 Sep 2014 22:32:12 GMT

Case Statement has the wrong syntax:

CASE <case-operand>

WHEN when-condition THEN result-expression

<...WHEN when-condition THEN 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

Re: Question on Proc SQL procedure with a case function

PGStats — Sun, 07 Sep 2014 22:35:37 GMT

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;

Re: Question on Proc SQL procedure with a case function

machete — Sun, 07 Sep 2014 22:54:42 GMT

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

Re: Question on Proc SQL procedure with a case function

stat_sas — Sun, 07 Sep 2014 23:18:46 GMT

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;

Re: Question on Proc SQL procedure with a case function

machete — Sun, 07 Sep 2014 23:25:19 GMT

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.

Re: Question on Proc SQL procedure with a case function

Tom — Sun, 07 Sep 2014 23:27:10 GMT

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

Re: Question on Proc SQL procedure with a case function

Tom — Sun, 07 Sep 2014 23:39:05 GMT

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

;

Re: Question on Proc SQL procedure with a case function

PGStats — Mon, 08 Sep 2014 00:09:37 GMT

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;

Re: Question on Proc SQL procedure with a case function

machete — Mon, 08 Sep 2014 00:22:48 GMT

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