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

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

michan22 — Sun, 02 Apr 2017 23:50:46 GMT

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!

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

SASKiwi — Mon, 03 Apr 2017 00:01:05 GMT

date_min = min(of date1-date16);
format date_min date9.l

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

Reeza — Mon, 03 Apr 2017 00:17:55 GMT

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!

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

art297 — Mon, 03 Apr 2017 00:49:05 GMT

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

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

michan22 — Mon, 03 Apr 2017 01:35:11 GMT

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!

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

art297 — Mon, 03 Apr 2017 01:38:41 GMT

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

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

Ksharp — Mon, 03 Apr 2017 02:00:51 GMT

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;

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

michan22 — Mon, 03 Apr 2017 02:58:17 GMT

Oh my gosh thank you! this is life changing!

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

Miracle — Mon, 03 Apr 2017 04:22:36 GMT

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

Thanks!

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

Ksharp — Mon, 03 Apr 2017 10:08:53 GMT

I don't know. Maybe read it literally .

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

art297 — Mon, 03 Apr 2017 12:22:09 GMT

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