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

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,

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

8 REPLIES 8
art297
Opal | Level 21

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)

Steelers_In_DC
Barite | Level 11

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

Steelers_In_DC
Barite | Level 11

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?

art297
Opal | Level 21

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.

Steelers_In_DC
Barite | Level 11

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.

Steelers_In_DC
Barite | Level 11

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.

TomKari
Onyx | Level 15

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

Steelers_In_DC
Barite | Level 11

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.

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
  • 8 replies
  • 1411 views
  • 0 likes
  • 3 in conversation