topic Re: Converting CASE WHEN(Proc SQL) into Data Step hash object lookup in SAS Programming

Converting CASE WHEN(Proc SQL) into Data Step hash object lookup

imdickson — Fri, 22 Mar 2019 06:36:34 GMT

Hi SAS Community. I am trying to convert PROC SQL statement with some columns having CASE WHEN statement into Data step hash object lookup for neater looking and some other factors set by my team.

Here is the challenge that I am facing. There are some CASE WHEN statement that is actually taking from multiple sources. For example:

proc sql;

create table a as

select 

case when type eq 'PMU' then Lng=ppp.Lng

when type eq 'PPU' then Lng=yyy.Lng

end as Lng

from b left join c

on b.Id=c.Id;

;

quit;

My Data Step Hash Object lookup will look something like this without the CASE WHEN statement, which i want to include in but dont know how.

Data SimResultCapLoad;
	
  if _n_=1 then do;
     declare hash h(dataset:"c)");
     h.definekey('Id');
     h.definedata('Lng');
     h.definedone();

  end;
  set netcap.SimResultCapLoad;

  *Lookup;
  rc=h.find();

run;

But how do i add the checking of CASE WHEN in?

Re: Converting CASE WHEN(Proc SQL) into Data Step hash object lookup

Kurt_Bremser — Fri, 22 Mar 2019 06:54:50 GMT

Your SQL is syntactically incorrect. You use table aliases ppp and yyy, but there are no tables for these in the from part.

Re: Converting CASE WHEN(Proc SQL) into Data Step hash object lookup

andreas_lds — Fri, 22 Mar 2019 07:00:53 GMT

Both steps you posted are not error-free and are using different datasets and variables, please fix these problems.

In a data step select/case would be replaced by select/when. The left join can be written as merge with by and in.

Re: Converting CASE WHEN(Proc SQL) into Data Step hash object lookup

Tom — Fri, 22 Mar 2019 13:10:55 GMT

You need to provide more details about what you are trying to do and include some simple example datasets to make it clearer.

From your brief explanation you probably just want to create formats from your different lookup tables.

Then you can use the PUTN() function to dynamically pick which format to use.

Even easier if you make the format names match the value of the TYPE variable.

data A;
  set B;
  LNG = putn(id,cats(type,'.'));
run;

If your ID variable is character then use PUTC() function instead and add $ prefix on the format names.

Re: Converting CASE WHEN(Proc SQL) into Data Step hash object lookup

SASKiwi — Sat, 23 Mar 2019 01:24:45 GMT

Personally I don't see any point in switching to hash look-ups unless you are gaining a clear functional or performance advantage.

@Tom 's look-up format approach is way easier to code and is as fast as hash look-ups in many circumstances.

I have DATA steps doing 30 plus format look-ups. There is no way you would ever want to code those using hashes unless you enjoy writing 10 times the amount of code. I also (very rarely) use hash but only when there is a clear advantage in doing so.

Re: Converting CASE WHEN(Proc SQL) into Data Step hash object lookup

Patrick — Sat, 23 Mar 2019 01:59:29 GMT

@imdickson

In a data step you'd address the CASE/WHEN syntax with an IF/THEN - and you lookup/assign values based of the outcome of this condition.

The (syntactically incorrect) SQL you've posted has a left join in it. So here if the relationship between the tables is 1:n with the table to the left having the n matching rows then a simple hash lookup won't do anymore but you need also to loop using the hash do_over method.

Re: Converting CASE WHEN(Proc SQL) into Data Step hash object lookup

imdickson — Mon, 25 Mar 2019 04:01:46 GMT

Hi everyone. I've figured out the way to do it. What i need to do is to add "If then else" condition after "a.find()" statement.

It is now working fine. Thanks everyone for trying to help me out there. Cheers.