DATA Step, Macro, Functions and more

Problems with If, then, else -statement

Reply
Occasional Contributor
Posts: 16

Problems with If, then, else -statement

[ Edited ]

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! 

Super User
Super User
Posts: 7,988

Re: Problems with If, then, else -statement

[ Edited ]

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. 

Occasional Contributor
Posts: 16

Re: Problems with If, then, else -statement

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?

Super User
Super User
Posts: 7,988

Re: Problems with If, then, else -statement

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;

 

Occasional Contributor
Posts: 16

Re: Problems with If, then, else -statement

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..

PROC Star
Posts: 7,491

Re: Problems with If, then, else -statement

[ Edited ]

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

 

Super User
Posts: 11,343

Re: Problems with If, then, else -statement


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?

Ask a Question
Discussion stats
  • 6 replies
  • 114 views
  • 0 likes
  • 4 in conversation