topic Re: Multiple IF, THEN, DO, ELSE not producing consistent results in SAS Programming

Multiple IF, THEN, DO, ELSE not producing consistent results

sas-inquirer — Mon, 20 Nov 2017 15:53:00 GMT

Good morning,

I have spent days trying to work the logic on some IF, THEN, DO, ELSE statements and every time I get one to work, the other doesn't.

I am using SAS 9.2. I have tried consecutive IF, THEN, DO statements to try to make the IF THEN ELSE statements read certain blocks of data but that hasn't been working for me. I have tried everything I know how to try.

Background:

The data is a result of comparing two datasets for differences using PROC COMPARE.

Need:

I need to be able to differentiate between a difference as a result of having a value missing in one dataset and not the other and when the difference is as a result of an actual difference (no missing values).

Issues:

I found I needed to remove the X's from difference because sometimes the if statements would work if the DIFF value was blank. I included two WANT2 tables to show a couple of examples of what I've tried. But I could have included 50. I can get everything to = 1 or MISSING, but not either.

data have;
infile datalines dlm = ':' truncover;
length SOURCE $10. ID1 $2. ID2 $2. PHONE $15.;
input SOURCE $ ID1 $ ID2 $ PHONE $ ;
datalines;
SOURCE1: 1: 1: 5555555:
SOURCE2: 1: 1: 5555555:
DIFF: 1: 1: .:
SOURCE1: 1: 2: :
SOURCE2: 1: 2: :
DIFF: 1: 2: .:
SOURCE1: 2: 1: 9999999:
SOURCE2: 2: 1: 
DIFF: 2: 1: XXXXXXX:
SOURCE1: 3: 1:  :
SOURCE2: 3: 1: 4444444:
DIFF: 3: 1: XXXXXXX:
SOURCE1: 4: 1: 4448888:
SOURCE2: 4: 1: 4444444:
DIFF: 4: 1: ...XXXX:
;
run;

 
data want;
set have;
if SOURCE = "DIFF" and index(PHONE,'X') then PHONE = "";
else PHONE = PHONE;
run;

data want2;
set have;
by 
ID1 ID2;
if SOURCE = "SOURCE2" then do;
if PHONE = "" and lag(PHONE) ne "" then do;
if SOURCE = "DIFF" then PHONE = "MISSING";
end;

else if PHONE ne "" and lag(PHONE) = "" then do;
if SOURCE = "DIFF" then PHONE = "MISSING";
end;

else if PHONE ne lag(PHONE) then do;
if SOURCE = "DIFF" then PHONE = '1';
end;

end;

run;

 

 

data want2;
set have;
by 
ID1 ID2;
if SOURCE = "SOURCE2" and PHONE = "" and lag(PHONE) ne "" then do;
if SOURCE = "DIFF" then PHONE = "MISSING";
end;

else if SOURCE = "SOURCE2" and PHONE ne "" and lag(PHONE) = "" then do;
if SOURCE = "DIFF" then PHONE = "MISSING";
end;

else if SOURCE = "SOURCE2" and PHONE ne lag(PHONE) then do;
if SOURCE = "DIFF" then PHONE = '1';
end;

run;

I would love the end result to be this:

SOURCE ID1 ID2 PHONE

SOURCE1 1 1 5555555

SOURCE2 1 1 5555555

DIFF 1 1

SOURCE1 1 2

SOURCE2 1 2

DIFF 1 2

SOURCE1 2 1 9999999

SOURCE2 2 1

DIFF 2 1 MISSING

SOURCE1 3 1

SOURCE2 3 1 4444444

DIFF 3 1 MISSING

SOURCE1 4 1 4448888

SOURCE2 4 1 4447777

DIFF 4 1 1

Where am I going wrong?

Re: Multiple IF, THEN, DO, ELSE not producing consistent results

PaigeMiller — Mon, 20 Nov 2017 16:06:05 GMT

While I haven't gone through this in a lot of detail, the LAG function inside an IF statement doesn't work the way you think it should work.

You need to use the LAG function in a command that always executes and is not inside an IF statement.

For example, in dataset WANT2, try this immediately after the BY command:

prevphone=lag(phone);

and of course use prevphone in the rest of the code.

Re: Multiple IF, THEN, DO, ELSE not producing consistent results

RW9 — Mon, 20 Nov 2017 16:07:18 GMT

The fact that you do not format your code in a readable way, and use upcase coding really makes your code hard to read. From what I gather from your post, I would imagine your issue is caused by using the lag() functions in if's. You can read several posts on here about why it is not a good idea to do this. For my money I would find it far simpler to transpose (yes, I know against what I normally suggest) and then do the conditionals:

data have;
  infile datalines dlm = ':' truncover;
  length source $10. id1 $2. id2 $2. phone $15.;
  input source $ id1 $ id2 $ phone $ ;
datalines;
SOURCE1: 1: 1: 5555555:
SOURCE2: 1: 1: 5555555:
DIFF: 1: 1: .:
SOURCE1: 1: 2: :
SOURCE2: 1: 2: :
DIFF: 1: 2: .:
SOURCE1: 2: 1: 9999999:
SOURCE2: 2: 1: 
DIFF: 2: 1: XXXXXXX:
SOURCE1: 3: 1:  :
SOURCE2: 3: 1: 4444444:
DIFF: 3: 1: XXXXXXX:
SOURCE1: 4: 1: 4448888:
SOURCE2: 4: 1: 4444444:
DIFF: 4: 1: ...XXXX:
;
run;

proc transpose data=have out=want;
  by id1 id2;
  var phone;
  id source;
run;

This should put all the necessary information on one row.

Re: Multiple IF, THEN, DO, ELSE not producing consistent results

sas-inquirer — Mon, 20 Nov 2017 16:24:26 GMT

Thank you for your response RW9. I will certainly not be using lag in IF statements any more. I like your idea of using transpose, but my actual data set is way too large for transpose to work. Thanks again (and I'll work on making my code more readable).

Re: Multiple IF, THEN, DO, ELSE not producing consistent results

sas-inquirer — Mon, 20 Nov 2017 16:29:01 GMT

Thank you PaigeMiller. I have learned my lesson with lag. Your suggestion worked great. I had to add some coding to get my desired results, but everything looks just the way it should. Thank you so much. I cannot wait to put this issue to rest!

data want3 (drop = phone_result_fill prevphone phone_result);
set want;
by 
ID1 ID2;

prevphone = lag(phone);

if source = "source2" then do;
if phone = "" and prevphone ne "" or phone ne "" and prevphone = "" then phone_result = "MISSING";
else if phone ne prevphone then phone_result = "1";
end;

retain phone_result_fill;
if not missing (phone_result) then phone_result_fill = phone_result;
phone_result = phone_result_fill;

if source = "DIFF" then phone = phone_result;

run;