Use Array to Check and Use Data

Reply
Contributor
Posts: 37

Use Array to Check and Use Data

I have a dataset where I have up to 26 columns numbered col??- col??. Each column may or may not have a date (the first column, col1, always has the earliest date).

I need to check col2 against col1 to see if there are 30 days or less between the two dates. If there are I need col1 date, otherwise I need col2 date and continue to do the same for all subsequent columns. I need to check each column against the previous column if the difference is never 30 days or more I need the col1 value. If there is an instance of greater than 30 days I need that column's date. If there are multiple columns that meet the "over 30 days" criteria, I just need the final over 30 days column value.

I am thinking an array may lead me in the right direction but I'm not sure.

I would so appreciate any help anyone could give me.

Please see the example file.

Jeff

Super User
Posts: 11,105

Re: Use Array to Check and Use Data

It would help to include what you expect the output to look like. I can think of several ways to assign values but the output may not be what you have in mind.

Contributor
Posts: 37

Re: Use Array to Check and Use Data

Hi Ballardw.

The output I would like would simply be the record key with the col1 date (if that is the earliest qualifying date) or the record key with the last date where the <= 30 days was true.

Jeff

Super User
Posts: 9,856

Re: Use Array to Check and Use Data

Suggest post some small data and output, that would be better.

if you have 26 variables , that said you have 325 combination ( pick 2 out of 26   - C 2 26). here is some dummy code .

array w{*} want1-want325 ;

array d{*} date1-date26;

do i=dim(d) to 2 by -1;

do j=i-1 to 1 by -1;

   n+1;

   w{n}=ifn( d{i} -d{j} < 30 ,d{j},d{i} )  ;

end;

end;

Xia Keshan

Contributor
Posts: 37

Re: Use Array to Check and Use Data

Hi Ksharp.

I did post an Excel example of the data. I tried your code and I get a "Array subscript out of range" on the "w{n}=ifn( d{i} -d{j} < 30 ,d{j},d{i} )  ;" line?

I'm really new to arrays so I'm sure I've not used this correctly.

Jeff

PROC Star
Posts: 7,431

Re: Use Array to Check and Use Data

Jeff: You posted an example of what your data looks like, but not the output you want based on that example.

Contributor
Posts: 37

Re: Use Array to Check and Use Data

Sorry, I misunderstood.

The dates given are program entry dates. If a person enters a program section within 30 days of the previous entry it is assumed to be the same contiguous program. Otherwise it is considered a new program start.

Here is a redo of the example about with suggested output.

Jeff

PROC Star
Posts: 7,431

Re: Use Array to Check and Use Data

I understand up through record 8, but don't understand why your example assigns Entry_Date_2 for the remaining records.

If that was an error, and they should have been missing, then the following might do what you want:

data want (drop=col1-col8);

  set have (rename=(Entry_Date=Entry_Date_1));

  format Entry_Date_2 date.;

  array cols(*) col1-col8;

  do _n_=1 to 8;

    if cols(_n_)-Entry_Date_1 ge 30 then do;

      Entry_Date_2=cols(_n_);

      leave;

    end;

  end;

run;

Ask a Question
Discussion stats
  • 7 replies
  • 568 views
  • 0 likes
  • 4 in conversation