Help using Base SAS procedures

proc sql; select case - end

Accepted Solution Solved
Reply
Valued Guide
Posts: 858
Accepted Solution

proc sql; select case - end

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,


Accepted Solutions
Solution
‎09-07-2012 11:00 AM
PROC Star
Posts: 7,363

Re: proc sql; select case - end

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.

View solution in original post


All Replies
PROC Star
Posts: 7,363

Re: proc sql; select case - end

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)

Valued Guide
Posts: 858

Re: proc sql; select case - end

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

Valued Guide
Posts: 858

Re: proc sql; select case - end

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?

Solution
‎09-07-2012 11:00 AM
PROC Star
Posts: 7,363

Re: proc sql; select case - end

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.

Valued Guide
Posts: 858

Re: proc sql; select case - end

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.

Valued Guide
Posts: 858

Re: proc sql; select case - end

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.

PROC Star
Posts: 1,091

Re: proc sql; select case - end

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

Valued Guide
Posts: 858

Re: proc sql; select case - end

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.

☑ This topic is SOLVED.

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

Discussion stats
  • 8 replies
  • 322 views
  • 0 likes
  • 3 in conversation