BookmarkSubscribeRSS Feed
hovliza
Obsidian | Level 7

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! 

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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. 

hovliza
Obsidian | Level 7

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?

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

 

hovliza
Obsidian | Level 7

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

art297
Opal | Level 21

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

 

ballardw
Super User

@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?

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 6 replies
  • 822 views
  • 0 likes
  • 4 in conversation