Help using Base SAS procedures

Question on Proc SQL procedure with a case function

Accepted Solution Solved
Reply
Contributor
Posts: 57
Accepted Solution

Question on Proc SQL procedure with a case function

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


Accepted Solutions
Solution
‎09-07-2014 07:39 PM
Super User
Super User
Posts: 7,036

Re: Question on Proc SQL procedure with a case function

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

;


View solution in original post


All Replies
Super User
Posts: 19,768

Re: Question on Proc SQL procedure with a case function

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

Respected Advisor
Posts: 4,919

Re: Question on Proc SQL procedure with a case function

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

PG
Contributor
Posts: 57

Re: Question on Proc SQL procedure with a case function

Hi,

The code you provided works but does not deliver the result I expected - i thought this should be the solution Smiley Sad 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

Super User
Super User
Posts: 7,036

Re: Question on Proc SQL procedure with a case function

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_) (Smiley Happy;

run;


A 3 3 3

A 1 1 0

A 2 2 0

B 3 2 2

B 1 1 0

Contributor
Posts: 57

Re: Question on Proc SQL procedure with a case function

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

Respected Advisor
Posts: 4,919

Re: Question on Proc SQL procedure with a case function

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

PG
Trusted Advisor
Posts: 1,228

Re: Question on Proc SQL procedure with a case function

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;

Contributor
Posts: 57

Re: Question on Proc SQL procedure with a case function

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.

Solution
‎09-07-2014 07:39 PM
Super User
Super User
Posts: 7,036

Re: Question on Proc SQL procedure with a case function

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

;


🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 5245 views
  • 3 likes
  • 5 in conversation