topic Re: Problems with If, then, else -statement in SAS Programming

Problems with If, then, else -statement

hovliza — Fri, 10 Nov 2017 16:00:17 GMT

Hi!

I was wondering what I am doing wrong. I want to make a starting point of my follow-up (date_start1) and I have different dates of measurement (date1, date2, date3 etc.) and I want to pick the date which is nearest to the date of questionnaire completion (dataq) as my date_start1. Most of the time date_start1 is date1. But sometimes date2-4 are closer to the date of questionnaire completion.

When I do this:

DATA want;
SET have;
date_start1=date1;
IF abs((date1-dataq)/365.25) GT abs((date2-dataq)/365.25) THEN date_start1=date2;
ELSE IF abs((date1-dataq)/365.25) OR abs((date2-dataq)/365.25) GT abs((date3-dataq)/365.25) THEN date_start1=date3;
ELSE IF abs((date1-dataq)/365.25) OR abs((date2-dataq)/365.25) OR abs((date3-dataq)/365.25) GT abs((date4-dataq)/365.25) THEN date_start1=date4;
FORMAT date_start1 DATE9.;
RUN;

I don't get good results. SAS doesn't pick this:

date_start1=date1;

It seems SAS only picks:

IF abs((date1-dataq)/365.25) GT abs((date2-dataq)/365.25) THEN date_start1=date2;

What should I do?

Thanks in advance!

Re: Problems with If, then, else -statement

RW9 — Fri, 10 Nov 2017 11:51:52 GMT

Well you could first start by providing some test data in the form of a datastep and what you want the output to look like. At a guess I would say, step one would be to work out the difference in days between each of the dates, then choosing the smallest, e.g:

data have;
  actual="12jan2017"d; date1="01jan2017"d; date2="14jan2017"d; date3="30jan2017"d; output;
  actual="01feb2017"d; date1="01jan2017"d; date2="14jan2017"d; date3="10feb2017"d; output;
  format _all_ date9.;
run;

data want (drop=i);
  set have;
  diff=abs(date1-actual);
  start=date1;
  array v{2} date2 date3;
  do i=1 to 2;
    if abs(v{i}-actual) < diff then do;
      start=v{i};
      diff=abs(v{i}-actual);
    end;
  end;
  format start date9.;
run;
run;

Just to add, I use arrays so its expandable - i.e. you could have date4 or date20, doesn't matter, just update the number. If its only 1 or 2, then maybe if statement for each is fine.

Re: Problems with If, then, else -statement

hovliza — Fri, 10 Nov 2017 13:12:41 GMT

Thanks for your suggestion. For my data, the syntax would be:

DATA want (drop=i);
SET have;
diff=abs((date1-dateq)/365.25);
start=date1;
array v{3} date2 date3 date4;
do i=1 to 3;
if abs(v{i}-dateq) < diff then do;
start=v{i};
diff=abs(v{i}-dateq);
END;
END;
FORMAT start date9.;
RUN;

But there are no values in my dataset.. Whats going wrong?

With this syntax:

data have;
  cdatalg="12jan2017"d; datesc1="01jan2017"d; datesc2="14jan2017"d; datesc3="30jan2017"d; datesc4="10nov2017"d; output;
  cdatalg="01feb2017"d; datesc1="01jan2017"d; datesc2="14jan2017"d; datesc3="10feb2017"d; datesc4="10dec2017"d; output;
  format _all_ date9.;
run;

I get values in my dataset, but not the closest by the dataq.. the dataset looks like;

	Dataq	Date1	Date2	Date3	Date4	Diff	start
1	12JAN2017	01JAN2017	14JAN2017	30JAN2017	10NOV2017	0.0301	01JAN2017
2	01JAN2017	14JAN2017	10FEB2017	10DEC2017	10DEC2017	0.0848	01JAN2017

What can I do?

Re: Problems with If, then, else -statement

RW9 — Fri, 10 Nov 2017 13:39:23 GMT

This is why you should provide test data in the form of a datastep - I cannot see your data!! All I can do is write my own which is what this does:

data have;
  actual="12jan2017"d; date1="01jan2017"d; date2="14jan2017"d; date3="30jan2017"d; output;
  actual="01feb2017"d; date1="01jan2017"d; date2="14jan2017"d; date3="10feb2017"d; output;
  format _all_ date9.;
run;

That gives me data which sounds like the data you have and have not shown me. Using this data I then write the next code:

data want (drop=i);
  set have;
  diff=abs(date1-actual);
  start=date1;
  array v{2} date2 date3;
  do i=1 to 2;
    if abs(v{i}-actual) < diff then do;
      start=v{i};
      diff=abs(v{i}-actual);
    end;
  end;
  format start date9.;
run;

Which runs on the test data I created. You need to modify it to your data which I cannot see as you have not provided it. If this is your data:

data have;
  cdatalg="12jan2017"d; datesc1="01jan2017"d; datesc2="14jan2017"d; datesc3="30jan2017"d; datesc4="10nov2017"d; output;
  cdatalg="01feb2017"d; datesc1="01jan2017"d; datesc2="14jan2017"d; datesc3="10feb2017"d; datesc4="10dec2017"d; output;
  format _all_ date9.;
run;

Then my step needs to be altered to be:

data want (drop=i);
  set have;
  diff=abs(datesc1-cdatalg);
  start=datesc1;
  array v{3} datesc2 datesc3 datesc4;
  do i=1 to 3;
    if abs(v{i}-cdatalg) < diff then do;
      start=v{i};
      diff=abs(v{i}-cdatalg);
    end;
  end;
  format start date9.;
run;

Re: Problems with If, then, else -statement

hovliza — Fri, 10 Nov 2017 15:16:46 GMT

Yes I understand.

data want;
  cdatalg="12jan2017"d; datesc1="01jan2017"d; datesc2="14jan2017"d; datesc3="30jan2017"d; datesc4="10nov2017"d; output;
  cdatalg="01feb2017"d; datesc1="01jan2017"d; datesc2="14jan2017"d; datesc3="10feb2017"d; datesc4="10dec2017"d; output;
  format _all_ date9.;
run;

DATA want (drop=i);
SET have;
diff=abs((datesc1-cdatalg)/365.25);
start=datesc1;
array v{3} datesc2 datesc3 datesc4;
do i=1 to 3;
if abs(v{i}-cdatalg) < diff then do;
start=v{i};
diff=abs(v{i}-cdatalg);
END;
END;
FORMAT start date9.;
RUN;

If i am doing this I get the data I've showed in my other reaction. But with only the first date as a start. But that's not right.
It is not possible for me to show my dataset, because of confidentially reasons..

Re: Problems with If, then, else -statement

art297 — Fri, 10 Nov 2017 16:55:42 GMT

Your code showed you creating dataset want, but then analyzing dataset have.

Also, your code does a division by 365.25. Is that necessary?

It appears that you may only need something like:

data have;
  cdatalg="12jan2017"d; datesc1="01jan2017"d; datesc2="14jan2017"d; datesc3="30jan2017"d; datesc4="10nov2017"d; output;
  cdatalg="01feb2017"d; datesc1="01jan2017"d; datesc2="14jan2017"d; datesc3="10feb2017"d; datesc4="10dec2017"d; output;
  format _all_ date9.;
run;

DATA want (drop=i);
  SET have;
  diff=999999;
  array v{*} datesc1-datesc4;
  do i=1 to dim(v);
    if abs(v{i}-cdatalg) < diff then do;
      start=v{i};
      diff=abs(v{i}-cdatalg);
    END;
  END;
  FORMAT start date9.;
RUN;

Art, CEO, AnalystFinder.com

Re: Problems with If, then, else -statement

ballardw — Mon, 13 Nov 2017 18:10:54 GMT

@hovliza wrote:

Hi!

I was wondering what I am doing wrong. I want to make a starting point of my follow-up (date_start1) and I have different dates of measurement (date1, date2, date3 etc.) and I want to pick the date which is nearest to the date of questionnaire completion (dataq) as my date_start1. Most of the time date_start1 is date1. But sometimes date2-4 are closer to the date of questionnaire completion.

When I do this:
DATA want;
SET have;
date_start1=date1;
IF abs((date1-dataq)/365.25) GT abs((date2-dataq)/365.25) THEN date_start1=date2;
ELSE IF abs((date1-dataq)/365.25) OR abs((date2-dataq)/365.25) GT abs((date3-dataq)/365.25) THEN date_start1=date3;
ELSE IF abs((date1-dataq)/365.25) OR abs((date2-dataq)/365.25) OR abs((date3-dataq)/365.25) GT abs((date4-dataq)/365.25) THEN date_start1=date4;
FORMAT date_start1 DATE9.;
RUN;
I don't get good results. SAS doesn't pick this:
date_start1=date1;
It seems SAS only picks:
IF abs((date1-dataq)/365.25) GT abs((date2-dataq)/365.25) THEN date_start1=date2;
What should I do?

Thanks in advance!

There is NO way for SAS to have a value of date_start1= date1. (unless date1 matches one of date2, 3 or 4)

Your if/then/else forces it to have one of the values of date2, date3 or date4.

Perhaps you meant to have another else that left date_start1 with a value of date1 or a different test at the start before the assignment of date2?