topic Re: proc sql; select case - end in SAS Procedures

proc sql; select case - end

Steelers_In_DC — Fri, 07 Sep 2012 13:48:16 GMT

Hello, I've been putting together some information with the assumption I was using select case correctly. It has seemed to be working but is not in this specific case. The variable names and titles I'm using are very different in length, in the past they have not been. I've gone back to verify that what I have been doing has been working and it seems that it has been but in this case it is not:

PROC SQL;

create table test as

select

case

when lacslink_addr_typ = 'G' then 'General Delivery'

when lacslink_ind = 'S' then 'Secondary number dropped'

when lacs_ind = 'L' then 'Lacs Hit'

when lacslink_ind = 'Y' then 'LacsLink Match'

when lacslink_ind = 'N' then 'No Match'

when lacslink_ind = 'F' then 'False Positive'

when lacslink_addr_typ = 'S' then 'Street Address'

when lacslink_addr_typ = 'F' then 'Firm'

when lacslink_addr_typ = 'H' then 'High Rise'

when lacslink_addr_typ = 'R' then 'Rural Route'

when lacslink_addr_typ = 'P' then 'PO Box'

else 'Missing'

end as LACS

from prd_eds.v_address_household

where ver_start_day_key <= &prev_lock and

ver_end_day_key > &prev_lock;

I am doing this to combine two dates, then merge and get the difference in between the two. I'm not getting the desired results and I'm wondering why. The only thing I can think of that is different is the length so I've moved around the order and it changes the results but still never gives the desired results. I'm wondering if I've been wrong about the syntax or the purpose of this exercise all along. Any help will be appreciated. Thank You,

Re: proc sql; select case - end

art297 — Fri, 07 Sep 2012 14:14:20 GMT

If changing the order of the statements resulted in changing the resulting file then I would have to ask: is the resulting field "LACS" meaningful?

E.g., if lacslink_addr_typ = 'G', the value will always be 'General Delivery'. It can only get one of the other values if lacslink_addr_typ ne 'G'.

The same type of logic is applied to all of the other statements. Does it really produce a single, usable value?

Art (AKA Steelers in Toronto)

Re: proc sql; select case - end

Steelers_In_DC — Fri, 07 Sep 2012 14:23:54 GMT

I'm not sure I understand, when 'g' then 'general delivery', if it's not g it has to be one of the other options given, else 'missing'. I noticed after I posted that I use 's' twice but they are for two different variables so I didn't think that would matter.

the resulting field lacs isn't meaningful. I picked it randomly to give the field a name. Does that matter?

Go Steelers

Re: proc sql; select case - end

Steelers_In_DC — Fri, 07 Sep 2012 14:48:13 GMT

I split the above code into three sections by variables, then put them back together in a data step formatting lacs to a reasonable length. That gets me the result that I was looking for. I still don't understand why the first code didn't work. I question what I've been doing up until this point and wonder if I've been missing errors in the past. Do you see anything in the original code that would prevent the desired results?

Re: proc sql; select case - end

art297 — Fri, 07 Sep 2012 15:00:11 GMT

I wasn't referring to the name LACS but, rather, what the field represents.

e.g., the way your query is designed, you will NEVER get any of the other errors when lacslink_addr_typ = 'G' then 'General Delivery'

when lacslink_ind = 'S' then 'Secondary number dropped' actually reads:

when lacslink_ind = 'S' AND lacslink_addr_typ NE 'G' then 'Secondary number dropped'. And, in such cases, you will never see any of the other errors.

when lacs_ind = 'L' then 'Lacs Hit', accordingly, actually reads:

when lacs_ind = 'L' and lacslink_ind NE 'S' AND lacslink_addr_typ NE 'G' then 'Lacs Hit'

etc, etc. If that result is meaningful and what you want, the code should be doing what it states. However, changing the order of the statements will definitely produce different results.

Re: proc sql; select case - end

Steelers_In_DC — Fri, 07 Sep 2012 15:16:53 GMT

That is very helpful, what I am attempting to do is get one variable named 'LACS', then I'm running a proc freq and seeing how the count changes over time. I'm still not sure why the order would change, all of the variables are unique to the naming convention.

I was thinking of 'when/then' statements as if it was if/then/end, running them in my mind independently of each other. I have a lot of work to get back through editing.

Thanks for help.

Re: proc sql; select case - end

Steelers_In_DC — Fri, 07 Sep 2012 15:28:27 GMT

I hope I'm not being too much of a bother but I have one more question. If you understand what I'm trying to do, have each one of the when/then statements as independent statements, is it possible to do that without writing each variable in it's on proc sql; statement? I'd like to put an end at the end of each line, so I can have all of the individual statements combined into the lacs column. I guess this is not the best way to go about that.

Re: proc sql; select case - end

TomKari — Fri, 07 Sep 2012 16:00:18 GMT

From your code, I'm wondering if you're trying to nest your CASE statements, in otherwords

"if a then if m then x

else y

else

if b then if n then w

else z"

This is quite possible, but the way your code is currently structured, you're doing

"if a then x1 else

if m then x2 else

if b then w1 else w2"

Which may account for why your results are varying. To do it with a CASE try

case

when a then case

when m then x

else y

end

when b then case

when n then w

else z

end

else v

end

I suspect the spacing will be a mess in this post, and I'm sure that I've missed or misplaced some ENDs and brackets, but this should give you the idea.

Tom

Re: proc sql; select case - end

Steelers_In_DC — Fri, 07 Sep 2012 16:21:20 GMT

What I'm trying to do is get a response for each variable independently of the others, I can write each variable separately in it's on proc sql; but there are over 500 variables that I'm trying to do this with so I didn't want to do that. It will be too much text and take too much time when I try to put them all together after.