SAS programming: select a numerical value with the oldest corresponding date

Accepted Solution Solved
Reply
Contributor
Posts: 47
Accepted Solution

SAS programming: select a numerical value with the oldest corresponding date

[ Edited ]

Hi guys!

Please help me!

I have 106 subjects/observations and 16 numerical variables (var1-var16) with a date corresponding to each numerical variable (date1-date16). The date variables (and their corresponding numerical variables) are ordered so that older dates are placed last for each subject (e.g. subject 1 may have values in all 16 numerical and date variables with 16 being the oldest, subject 2 may only have 8 numerical and date variables with 8 being the oldest).

Now, I need to define a baseline_var that correspond to the numerical value with the oldest date for each subject. How can I program SAS to find this baseline value? 

Thank you!


Accepted Solutions
Solution
‎04-02-2017 10:58 PM
Super User
Posts: 9,681

Re: SAS programming: select a numerical value with the oldest corresponding date

Arthur.T,

Use function coalesce(),no need array.

 

data have;
  infile cards truncover;
  input var1-var16;
  cards;
1 2 5 7 6
2 2 4 5
3 4 2 7 9
9 8 7 6 5 3 2 8 1
;

data want;
  set have;
 want=coalesce(of var16-var1);
run;

View solution in original post


All Replies
Super User
Posts: 3,110

Re: SAS programming: select a numerical value with the oldest corresponding date

date_min = min(of date1-date16);
format date_min date9.l 
Super User
Posts: 17,840

Re: SAS programming: select a numerical value with the oldest corresponding date

Assuming no ties, and this is untested. 

You can find the largest value using the MAX function and then the corresponding index using WHICHN. I've nested these, but you may need to separate out the calculations.

 

If you know that the last record is the largest and missing values are used for the renaming values you can also count how many missing values are present (NMISS). Then the largest index would be something like the commented out option.

 

array vars(*) var1-var16;
array dates(*) date1-date16;

index_largest = whichn(max(of dates(*)), of dates(*)); 
*index_largest = dim(vars) - nmiss(of vars(*));
*index_largest = 16 - nmiss(of vars(*)); value = vars(index_largest);

 


michan22 wrote:

Hi guys!

Please help me!

I have 106 subjects/observations and 16 numerical variables (var1-var16) with a date corresponding to each numerical variable (date1-date16). The date variables (and their corresponding numerical variables) are ordered so that older dates are placed last for each subject (e.g. subject 1 may have values in all 16 numerical and date variables with 16 being the oldest, subject 2 may only have 8 numerical and date variables with 8 being the oldest).

Now, I need to define a baseline_var that correspond to the numerical value with the oldest date for each subject. How can I program SAS to find this baseline value? 

Thank you!


 

PROC Star
Posts: 7,363

Re: SAS programming: select a numerical value with the oldest corresponding date

Obviously, a number of us are interpreting your question differently. I think the following is what you want:

 

data have;
  infile cards truncover;
  input var1-var16;
  cards;
1 2 5 7 6
2 2 4 5
3 4 2 7 9
9 8 7 6 5 3 2 8 1
;

data want;
  set have;
  array nums(*) var1-var16;
  do _n_=dim(nums) to 1 by -1;
    if not missing(nums(_n_)) then do;
      want=nums(_n_);
      leave;
    end;
  end;
run;

Art, CEO, AnalystFinder.com

 

Contributor
Posts: 47

Re: SAS programming: select a numerical value with the oldest corresponding date

Hi guys! thanks for all the advice!

Maybe this will clear up my question. So my dateset looks like this:

var1       date1      var2      date2      var3      date3       var4     date4      .......   var16    date16

200    08/09/16     300    05/02/16   400     06/10/15    500    01/20/15   .......    350     02/15/05

250    07/06/16     200    04/10/14

.

.

 

so for the 1st observation my baseline would be 350 because that's value corresponding to the oldest date, and 2nd observation would be 200 because that correspond to the oldest date.

I want the numerical value that correspond to the oldest date to be the baseline and I need to find this baseline for each subject.

Thank you!

PROC Star
Posts: 7,363

Re: SAS programming: select a numerical value with the oldest corresponding date

That's precisely what the code I suggested does. The date variables are irrelevant as long as they are in date order.

 

Art, CEO, AnalystFinder.com

 

Solution
‎04-02-2017 10:58 PM
Super User
Posts: 9,681

Re: SAS programming: select a numerical value with the oldest corresponding date

Arthur.T,

Use function coalesce(),no need array.

 

data have;
  infile cards truncover;
  input var1-var16;
  cards;
1 2 5 7 6
2 2 4 5
3 4 2 7 9
9 8 7 6 5 3 2 8 1
;

data want;
  set have;
 want=coalesce(of var16-var1);
run;
Contributor
Posts: 47

Re: SAS programming: select a numerical value with the oldest corresponding date

Oh my gosh thank you! this is life changing!

 

Regular Contributor
Posts: 217

Re: SAS programming: select a numerical value with the oldest corresponding date

By the way, why is this function called coalesce? Just out of curiosity so it will help me to remembering it more easier.

Thanks! Smiley Happy

Super User
Posts: 9,681

Re: SAS programming: select a numerical value with the oldest corresponding date

I don't know. Maybe read it literally .

PROC Star
Posts: 7,363

Re: SAS programming: select a numerical value with the oldest corresponding date

My guess is that it comes from the original definition in computer science, namely: the merging of adjacent blocks of memory to fill gaps caused by deallocated memory

 

Art, CEO, AnalystFinder.com

 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 219 views
  • 5 likes
  • 6 in conversation