BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
machete
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

9 REPLIES 9
Reeza
Super User

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
Opal | Level 21

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
machete
Calcite | Level 5

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

Tom
Super User Tom
Super User

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
Calcite | Level 5

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
Opal | Level 21

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
stat_sas
Ammonite | Level 13

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
Calcite | Level 5

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
Super User Tom
Super User

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

;


sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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