BookmarkSubscribeRSS Feed
Jeff_DOC
Pyrite | Level 9

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

7 REPLIES 7
ballardw
Super User

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.

Jeff_DOC
Pyrite | Level 9

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

Ksharp
Super User

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

Jeff_DOC
Pyrite | Level 9

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

art297
Opal | Level 21

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

Jeff_DOC
Pyrite | Level 9

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

art297
Opal | Level 21

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;

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