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
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.
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
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
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
Jeff: You posted an example of what your data looks like, but not the output you want based on that example.
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
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;
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.
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.