topic Re: IF-THEN statement not working for all observations despite logic being met in SAS Data Management

IF-THEN statement not working for all observations despite logic being met

jpsmith — Wed, 31 Jul 2019 21:36:42 GMT

Hello,

I have a basic longitudinal dataset that contains a study ID (id), visit number (visitno; numeric, 1-4), and some dichotomous symptoms variables (i.e. if the patient had a cough or not). I am wanting to know if the symptoms are resolved from the previous visit.

In one of the most basic SAS data management functions, I am working with an if-then statement and the lag() function to create two new variables - i.e. cough_lag and cough_rtt (response to treatment). Curiously, the statement does not work for all observations.

For instance, for the "cough" symptom, it does not work for 9 SIDs, all of which:

are on the second visit
have cough=0 and cough_lag=1

See freq output below. After racking my brain, going through the code, changing dataset names, skimming the logs, closing out SAS and starting it back up, etc, I assume there is something very basic that I am overlooking. Thus, I am turning to you to help!

The only think I can think of is that in the previous data step I removed some of the visits prior to visit 2 (visits 1,1, 1.2, 1.3, etc) that indicated intermediate visits between visit 1 and 2. I used a "if visitno not in(1,2,3,4) then delete;" statement in the previous data step. But in all these visits, all of these symptoms were missing, so I am not sure why only certain ones are singled out. I will be happy to put the data2datastep macro output for dataset "data10" used to set the below code, but when I open a new SAS program and run the output that I was going to post here, there doesn't seem to be the issue. Does anyone have a workaround?

The logic code is below.

/*Determine resolution of symptoms*/
data data11; set data10;
	array a {4} cough fever weightloss cervadeno;
	array b	{4} cough_lag fever_lag weightloss_la cervadeno_lag;
	array c {4} cough_rtt fever_rtt weightloss_rtt cervadeno_rtt;
	do i=1 to 4;
		b{i}=lag(a{i});
		if a{i}=1 and b{i}=1 then c{i}=0;	/*No resolution os symptoms*/
		if a{i}=0 and b{i}=1 then c{i}=1;	/*Resolution of symptoms*/
		if a{i}=1 and b{i}=0 then c{i}=2;	/*Onset of new symptoms**/
		if a{i}=0 and b{i}=0 then c{i}=3;	/*N/A - No cough symptoms*/
	end; drop i; 
run;

A screenshot of the proc freq is below (only for visit 2), showing the 9 SIDs where the procedure didn't work.

Re: IF-THEN statement not working for all observations despite logic being met

Tom — Wed, 31 Jul 2019 22:58:56 GMT

Please post a minimal set of input data that demonstrates the problem.

Is this data all for the same subject? Because your program has nothing to prevent the first values for a subject from being compared to the last values from the previous subject.

Re: IF-THEN statement not working for all observations despite logic being met

jpsmith — Thu, 01 Aug 2019 13:06:16 GMT

Hi Tom,

Thank you for your response - this morning I created several datasets using the data2datastep macro but each time I reran the code, this problem didn't appear.

[edited - my earlier response indicated that an intermediate data step solved the problem, but it did not actually solve the problem]

The "data10" data step was where, among other things, I dropped the "1.x" intermediate visits. The truncated code is below:

data data10; set data9; by id; retain t;
...
	if first.id then t=antitbtreat; else antitbtreat=t; drop t;
	if visitno not in(1,2,3,4) then delete; 
...
run;
proc sort data=data10; by id; run;

The issue wasn't all in the same study id. Since I am only concerned with symptom resolution at visit 2, I'm not concerned about visit 1 pulling from visit 4 across study IDs (I did code in for the response to treatment at visit 1 to be set to missing, I am only posting truncated code as to not clutter up the board).

Thank you again for your time,

Jonathan

Re: IF-THEN statement not working for all observations despite logic being met

Tom — Thu, 01 Aug 2019 13:07:43 GMT

If you want help you need to post data. Post what you have (at whatever step you want to start with), what you expect to get, the code you ran and an explanation of why the output is not what you expected.

Re: IF-THEN statement not working for all observations despite logic being met

jpsmith — Thu, 01 Aug 2019 13:28:15 GMT

Tom,

Thank you again - below I am posting code for a truncated dataset with 10 study IDs. In this code, running the proc freq at the end shows that the error occurs in one study ID (K6002).

I would expect all study IDs with cough=0 and cough_lag=1 to have a cough_rtt=1.

In the frequency table, SID K6002 has a cough=0 and cough_lag=1 but a cough_rtt that is missing (".") despite the same pattern.

In this dataset, I would expect 4 people to have 0-1-1 permutation of these three variables, but instead I have 3 with 0-1-1 and 1 with 0-1-"."

options nofmterr;
data WORK.A;
  infile datalines dsd truncover;
  input id:$500. visitno:32. prescrinst:PRESCRINST_. dateofscr:YYMMDD10. dateofreview_005:YYMMDD10. prescrcough:SYMPTOMS_032___2_. cough_005:COUGH_005_. prescrfever:SYMPTOMS_032___1_. fever_005:FEVER_005_. muac:BEST12. weightloss_005:WEIGHTLOSS_005_. prescrwasted:SYMPTOMS_032___3_. prescrlan:SYMPTOMS_032___6_. cervadeno_005:CERVADENO_005_. recvdnutrisupp_005:RECVDNUTRISUPP_005_. malnuttxgiven:MALNUTTXGIVEN_. fevtxgiven:FEVTXGIVEN_. fevantibiotx:FEVANTIBIOTX_. recvantibiofever_005:RECVANTIBIOFEVER_005_. coughtxgiven:COUGHTXGIVEN_. coughantibiotxgiven:COUGHANTIBIOTXGIVEN_. recvantibiocough_005:RECVANTIBIOCOUGH_005_. antitbtreat:ANTITBTREAT_. cough:32. fever:32. weightloss:32. cervadeno:32.;
  format prescrinst PRESCRINST_. dateofscr YYMMDD10. dateofreview_005 YYMMDD10. prescrcough SYMPTOMS_032___2_. cough_005 COUGH_005_. prescrfever SYMPTOMS_032___1_. fever_005 FEVER_005_. muac BEST12. weightloss_005 WEIGHTLOSS_005_. prescrwasted SYMPTOMS_032___3_. prescrlan SYMPTOMS_032___6_. cervadeno_005 CERVADENO_005_. recvdnutrisupp_005 RECVDNUTRISUPP_005_. malnuttxgiven MALNUTTXGIVEN_. fevtxgiven FEVTXGIVEN_. fevantibiotx FEVANTIBIOTX_. recvantibiofever_005 RECVANTIBIOFEVER_005_. coughtxgiven COUGHTXGIVEN_. coughantibiotxgiven COUGHANTIBIOTXGIVEN_. recvantibiocough_005 RECVANTIBIOCOUGH_005_. antitbtreat ANTITBTREAT_.;
  label id="Record ID" prescrinst="Pre-screening instance" dateofscr="2. Date of screening" dateofreview_005="Date of Review" prescrcough="Check all appropriate symptoms: (choice=Persistent cough)" cough_005="1. Cough" prescrfever="Check all appropriate symptoms: (choice=Prolonged fever)" fever_005="4. Fever?" muac="9. MUAC (cm) (if >6 months)" weightloss_005="3. Does the parent think the child has lost weight?" prescrwasted="Check all appropriate symptoms: (choice=Wasted)" prescrlan="Check all appropriate symptoms: (choice=Visible cervical lymph node)" cervadeno_005="11. Painless, hard or soft cervcal adenopathies?" recvdnutrisupp_005="3b. Did the child receive nutritional supplements?" malnuttxgiven="ii. Treatment for malnutrition given?" fevtxgiven="ii. Treatment given?" fevantibiotx="iii-a: If treatment given, antibiotic treatment?" recvantibiofever_005="4b. Did the child receive antibiotic treatment?" coughtxgiven="ii. Treatment given?" coughantibiotxgiven="ii-a: If treatment given, antibiotic treatment?" recvantibiocough_005="1b. Did the child receive antibiotic treatment for cough?" antitbtreat="f. Anti-TB treatment";
datalines4;
K6001,1,,2013-10-03,2013-10-03,,,,,12.4,,,,No,,,,,,,,,No,,,,0
K6001,1.1,First,2013-10-03,,Checked,,Unchecked,,,,Unchecked,Unchecked,,,,,,,Yes,Yes,,,1,0,0,
K6001,2,,2013-10-03,2013-10-17,,Yes,,No,13,No,,,No,,,,,,,,No,,1,0,0,0
K6001,3,,2013-10-03,2013-11-21,,Yes,,No,13.4,No,,,No,,,,,,,,No,,1,0,0,0
K6001,4,,2013-10-03,2014-03-27,,Yes,,No,15,No,,,No,,,,,,,,No,,1,0,0,0
K6002,1,,2013-10-07,2013-10-08,,,,,,,,,No,,,,,,,,,No,,,,0
K6002,1.1,First,2013-10-07,,Checked,,Unchecked,,,,Checked,Unchecked,,,Yes,,,,Yes,Yes,,,1,0,1,
K6002,2,,2013-10-07,2013-10-22,,No,,No,12,No,,,No,,,,,,,,,,0,0,0,0
K6002,3,,2013-10-07,2013-11-25,,,,,12,,,,No,,,,,,,,,,,,,0
K6002,4,,2013-10-07,2014-03-19,,No,,Yes,14,No,,,No,,,,,No,,,,,0,1,0,0
K6003,1,,2013-10-08,2013-10-08,,,,,,,,,No,,,,,,,,,Yes,,,,0
K6003,1.1,First,2013-10-08,,Checked,,Unchecked,,,,Unchecked,Unchecked,,,,,,,Yes,Yes,,,1,0,0,
K6003,2,,2013-10-08,2013-10-24,,Yes,,No,12.4,No,,,No,,,,,,,,Yes,,1,0,0,0
K6003,3,,2013-10-08,2013-11-25,,No,,No,14,No,,,No,,,,,,,,,,0,0,0,0
K6003,4,,2013-10-08,2014-03-19,,No,,No,14.3,,,,No,,,,,,,,,,0,0,,0
K6004,1,,2013-10-14,2013-10-14,,Yes,,Yes,,Yes,,,No,Yes,,,,Yes,,,Yes,No,,,,0
K6004,1.2,Second,2013-10-14,,Checked,,Checked,,,,Checked,Unchecked,,,Yes,Yes,Yes,,Yes,Yes,,,1,1,1,
K6004,2,,2013-10-14,2013-10-27,,No,,No,10.5,No,,,No,,,,,,,,,,0,0,0,0
K6004,3,,2013-10-14,2013-12-09,,No,,No,13.1,No,,,No,,,,,,,,,,0,0,0,0
K6004,4,,2013-10-14,2014-03-31,,Yes,,No,12.7,Yes,,,No,Yes,,,,,,,No,,1,0,1,0
K6005,1,,2013-10-22,2013-10-22,,,,,,,,,No,,,,,,,,,No,,,,0
K6005,1.1,First,2013-10-22,,Checked,,Checked,,,,Checked,Unchecked,,,Yes,No,,,Yes,Yes,,,1,1,1,
K6005,2,,2013-10-22,2013-11-07,,,,,11.6,,,,,,,,,,,,,,,,,
K6005,3,,2013-10-22,2013-12-18,,No,,No,11.3,No,,,No,,,,,,,,,,0,0,0,0
K6005,4,,2013-10-22,2014-04-09,,No,,No,14.5,No,,,No,,,,,,,,,,0,0,0,0
K6007,1,,2013-10-23,2013-10-23,,,,,,,,,No,,,,,,,,,No,,,,0
K6007,1.2,Second,2013-10-23,,Unchecked,,Unchecked,,,,Checked,Unchecked,,,Yes,,,,,,,,0,0,1,
K6007,2,,2013-10-23,2013-11-04,,,,,9.8,,,,,,,,,,,,,,,,,
K6007,3,,2013-10-23,2013-12-17,,No,,No,12.6,No,,,No,,,,,,,,,,0,0,0,0
K6007,4,,2013-10-23,2014-04-09,,No,,No,13.6,No,,,No,,,,,,,,,,0,0,0,0
K6008,1,,2013-10-29,2013-10-29,,,,,,,,,No,,,,,,,,,Yes,,,,0
K6008,1.3,Third,2013-10-29,,Checked,,Unchecked,,,,Unchecked,Unchecked,,,,,,,Yes,Yes,,,1,0,0,
K6008,2,,2013-10-29,2013-11-11,,No,,No,,,,,No,,,,,,,,,,0,0,,0
K6008,3,,2013-10-29,2013-12-17,,Yes,,No,12,No,,,No,,,,,,,,No,,1,0,0,0
K6008,4,,2013-10-29,2014-04-10,,No,,No,14,No,,,No,,,,,,,,,,0,0,0,0
K6009,1,,2013-10-30,2013-10-30,,,,,14,,,,No,,,,,,,,,No,,,,0
K6009,1.1,First,2013-10-30,,Checked,,Unchecked,,,,Unchecked,Unchecked,,,,,,,Yes,Yes,,,1,0,0,
K6009,2,,2013-10-30,2013-11-12,,Yes,,No,14.5,No,,,No,,,,,,,,No,,1,0,0,0
K6009,3,,2013-10-30,2014-01-13,,No,,No,14.9,No,,,No,,,,,,,,,,0,0,0,0
K6009,4,,2013-10-30,,,,,,,,,,,,,,,,,,,,,,,
K6010,1,,2013-10-31,2013-10-31,,,,,9.5,,,,No,,,,,,,,,Yes,,,,0
K6010,1.2,Second,2013-10-31,,Checked,,Checked,,,,Checked,Unchecked,,,Yes,Yes,Yes,,No,,,,1,1,1,
K6010,2,,2013-10-31,2013-11-13,,No,,No,9.5,,,,No,,,,,,,,,,0,0,,0
K6010,3,,2013-10-31,2013-12-19,,No,,No,12,No,,,No,,,,,,,,,,0,0,0,0
K6010,4,,2013-10-31,,,,,,,,,,,,,,,,,,,,,,,
K6011,1,,2013-10-31,2013-10-31,,,,,13.4,,,,No,,,,,,,,,No,,,,0
K6011,1.1,First,2013-10-31,,Unchecked,,Checked,,,,Unchecked,Unchecked,,,,Yes,Yes,,,,,,0,1,0,
K6011,2,,2013-10-31,2013-11-14,,No,,No,13.3,No,,,No,,,,,,,,,,0,0,0,0
K6011,3,,2013-10-31,2013-12-23,,No,,No,14,No,,,No,,,,,,,,,,0,0,0,0
K6011,4,,2013-10-31,2014-04-17,,No,,No,14,No,,,No,,,,,,,,,,0,0,0,0
;;;;

/*Pulling in symptom data from the 1.xx visit to visit 1*/
proc expand	data=a out=b; 
	by id;
	convert cough=cough_lead/transform=(lead);
	convert fever=fever_lead/transform=(lead);
	convert weightloss=weightloss_lead/transform=(lead);
run;

/*Setting these variables the lead variable*/
data c; set b; by id; 
	if visitno=1 then do;
		cough=cough_lead; 
		fever=fever_lead;
		weightloss=weightloss_lead;
	end;
	drop 	cough_lead fever_lead weightloss_lead
			prescrinst prescrcough prescrfever prescrwasted;
run;
proc sort data=c; by id visitno; run;

/*Dropping the intermediate visits, etc*/
data d; set c; by id; 
	retain t;
	if first.id then t=antitbtreat; else antitbtreat=t; drop t;
	daysdiff=dateofreview_005-dateofscr;
	label 	visitno="Visit" cough="Cough" fever="Fever" weightloss="Weight Loss" cervadeno="Cervcal Adenopathies" dateofscr="Date of Screening"
			muac="MUAC (cm) (if >6 months)" antitbtreat="Anti-TB Treatment" daysdiff="Time between Visits (Days)";
	if visitno not in(1,2,3,4) then delete; 
	drop time cough_005 fever_005 weightloss_005--recvantibiocough_005 antitbtreat;
run;

/*Creating response to treatment*/
data e; set d;
	array a {4} cough 		fever 		weightloss 		cervadeno;
	array b	{4} cough_lag	fever_lag	weightloss_lag	cervadeno_lag;
	array c {4} cough_rtt	fever_rtt	weightloss_rtt	cervadeno_rtt;
	do i=1 to 4;
		b{i}=lag(a{i});
		if a{i}=1 and b{i}=1 then c{i}=0;	/*No resolution os symptoms*/
		if a{i}=0 and b{i}=1 then c{i}=1;	/*Resolution of symptoms*/
		if a{i}=1 and b{i}=0 then c{i}=2;	/*Onset of new symptoms**/
		if a{i}=0 and b{i}=0 then c{i}=3;	/*N/A - No cough symptoms*/
	end; drop i; 
run;

/*check RTT*/
proc freq data=e; where visitno=2; tables cough*cough_lag*cough_rtt/list missing nocum norow nocol nopercent; run;

Re: IF-THEN statement not working for all observations despite logic being met

Tom — Thu, 01 Aug 2019 13:46:26 GMT

As I asked before.

Why doesn't data step E have a BY ID statement like the other steps?

Re: IF-THEN statement not working for all observations despite logic being met

jpsmith — Thu, 01 Aug 2019 13:48:55 GMT

Hi Tom,
This may have been an inadvertent error when changing the code around to try to solve the problem. Even with the BY ID statement, the issue still remains. Thank you!

Re: IF-THEN statement not working for all observations despite logic being met

Tom — Thu, 01 Aug 2019 13:54:23 GMT

So which values of ID are producing the wrong "answer" for VISITNO=2?

What is the right answer for those IDs?

Re: IF-THEN statement not working for all observations despite logic being met

jpsmith — Thu, 01 Aug 2019 13:59:49 GMT

In this dataset, one example is that this issue appears for SID K6002 for cough. It presents cough_rtt missing when it should be 1 for visit 2. Below is a screenshot of my freq output for cough at visit 2, and the print of SID K6002 for the cough variables.

Re: IF-THEN statement not working for all observations despite logic being met

Tom — Thu, 01 Aug 2019 14:50:01 GMT

Seems to be working for that ID.

data d;
  input id $ visitno cough ;
cards;
K6002      1        1  
K6002      2        0  
K6002      3        .  
K6002      4        0
;

data e;
  set d;
  by id;
  array a cough     /*fever   weightloss   cervadeno*/;
  array b cough_lag /*fever_lag weightloss_lag cervadeno_lag*/;
  array c cough_rtt /*fever_rtt weightloss_rtt cervadeno_rtt*/;
  do i=1 to dim(a);
    b[i]=lag(a[i]);
  end;
  if first.id then call missing(of b[*]);
  do i=1 to dim(a);
    if a[i]=1 and b[i]=1 then c[i]=0; /*No resolution os symptoms*/
    if a[i]=0 and b[i]=1 then c[i]=1; /*Resolution of symptoms*/
    if a[i]=1 and b[i]=0 then c[i]=2; /*Onset of new symptoms**/
    if a[i]=0 and b[i]=0 then c[i]=3; /*N/A - No cough symptoms*/
  end; 
  drop i; 
run;

                                    cough_    cough_
Obs     id      visitno    cough      lag       rtt

 1     K6002       1         1         .         .
 2     K6002       2         0         1         1
 3     K6002       3         .         0         .
 4     K6002       4         0         .         .

Perhaps your attempt to use PROC FREQ to summarize is the issue?

Perhaps your logic to get to step D is the issue? Why is COUGH missing for VISTNO=3?

How do you want to treat the missing values? Do you want to treat them as FALSE?

Perhaps you just need to change the IF/THEN logic.

    if a[i] and b[i] then c[i]=0; /*No resolution os symptoms*/
    else if b[i] then c[i]=1; /*Resolution of symptoms*/
    else if a[i] then c[i]=2; /*Onset of new symptoms**/
    else c[i]=3; /*N/A - No cough symptoms*/

                                    cough_    cough_
Obs     id      visitno    cough      lag       rtt

 1     K6002       1         1         .         2
 2     K6002       2         0         1         1
 3     K6002       3         .         0         3
 4     K6002       4         0         .         3

Re: IF-THEN statement not working for all observations despite logic being met

jpsmith — Thu, 01 Aug 2019 16:17:46 GMT

Works! When I redesigned the logic based on your recommendation of the omission of "=1" it works:

	do i=1 to 4;
		b{i}=lag(a{i});
		if a{i} and b{i} then c{i}=0;	/*No resolution os symptoms*/
		if a{i}=0 and b{i} then c{i}=1;	/*Resolution of symptoms*/
		if a{i} and b{i}=0 then c{i}=2;	/*Onset of new symptoms**/
		if a{i}=0 and b{i}=0 then c{i}=3;	/*N/A - No cough symptoms*/

compared to this, which doesn't work:

	b{i}=lag(a{i});
		if a{i}=1 and b{i}=1 then c{i}=0;	/*No resolution os symptoms*/
		if a{i}=0 and b{i}=1 then c{i}=1;	/*Resolution of symptoms*/
		if a{i}=1 and b{i}=0 then c{i}=2;	/*Onset of new symptoms**/
		if a{i}=0 and b{i}=0 then c{i}=3;	/*N/A - No cough symptoms*/

Do you know exactly why? Thank you!

Re: IF-THEN statement not working for all observations despite logic being met

Tom — Thu, 01 Aug 2019 16:52:10 GMT

Three points.

1) I changed you step to properly reset the lagged values to missing when starting a new ID.

2) In boolean expressions SAS will treat 0 or missing as FALSE and any other value as TRUE. No need to test if equal to 1.

3) I changed your logic to use IF/THEN/ELSE to make sure that it covers all possible combinations of the two boolean values.

So this logic:

    if a[i] and b[i] then c[i]=0; /*No resolution os symptoms*/
    else if b[i] then c[i]=1; /*Resolution of symptoms*/
    else if a[i] then c[i]=2; /*Onset of new symptoms**/
    else c[i]=3; /*N/A - No cough symptoms*/

Maps

TT to 0
FT to 1
TF to 2
FF to 3

Your logic is still messed up as you are not really showing what to do for all possible combinations.

Build yourself an example dataset with all possible combinations of A and B values and see what values of C get created.

data have;
  do A=.,0,1 ;
  do B=.,0,1 ;
    output;
  end;
  end;
run;

data want;
  set have;

* Your Logic ;
  if a and b then c=0;     /*No resolution os symptoms*/
  if a=0 and b then c=1;   /*Resolution of symptoms*/
  if a and b=0 then c=2;   /*Onset of new symptoms**/
  if a=0 and b=0 then c=3; /*N/A - No cough symptoms*/

* Corrected Logic ;
  if a and b then d=0; /*No resolution os symptoms*/
  else if b then d=1; /*Resolution of symptoms*/
  else if a then d=2; /*Onset of new symptoms**/
  else d=3; /*N/A - No cough symptoms*/

run;

proc print;
run;

Obs    A    B    c    d

 1     .    .    .    3
 2     .    0    .    3
 3     .    1    .    1
 4     0    .    .    3
 5     0    0    3    3
 6     0    1    1    1
 7     1    .    .    2
 8     1    0    2    2
 9     1    1    0    0

If you want to treat missing as different from 0 then add more logic to handle those cases.