Using arrays to identify specific column in a dataset

[simonae]

Hi all,

I am a relatively new user to SAS, and very new to arrays, and I am trying to use an array to do the following....

I have a data set that looks like this (below), with data recorded once per month for 21 months (from Jan 2016 to Sept 2018).

ID   Start_Date           Jan-2016    Feb-2016    March-2016     April-2016   .... Sept-2018

1     July 2015            2                 6                    1                      9               .....        7

2     August 2015       6                 4                    3                      6                .....       4

3     October 2015      8                 2                    5                      5               .....       8

For each participant, I need to grab the value from the columns that correspond with 6, 12, 18 and 24 months after an individual's Start_Date. So for example, for ID=1, because their start date is July 2015, I would need the values from the column Jan-2016(6mo), July-2016(12mo), Jan-2017(18mo), and July-2018(24mo)

I would like my new data set to include 4 new variables as 4 new columns, with the titles 6mo, 12mo, 18mo, 24mo (as shown below). For each of those new variables, I would like the value to match that of the value under the corresponding month gap from the start date.

ID  Start_Date          Jan-2016    Feb-2016   March-2016   April-2016   .... July-2018   6mo   12mo  18mo  24mo

1    July 2015            2                 6                    1                      9        .....            7         2          ...      ...            7

2    August 2015        6                 4                    3                      6        .....           4

3    October 2015      8                 2                    5                      5        .....           8

The code I am starting with is below, but I haven't figured out how to adapt it to my specific scenario.

data want;
set have;
array month{*} month1-month21;
array newmonth{*} month1-month21;
do i=1 to 21;
newmonth{i}=month{start_date+i-1};
end;
drop month: i;
run;

Hope this is helpful background. Any assistance is very much appreciated - thank you all in advance!

[ErikLund_Jensen]

Hi @simonae 

It would be much easier if you gave us some test data to work with. It is impossible to give a qualifed answer without running the code and verifying the result, and it takes a lot of time to create an input data set from your text description, and besides there is no guarantee that we get it right. so PLEASE supply a data step that can build an input data set. Like this (taken from another post):

data test;
input firm $2. year Month value1 value2 value3;
cards;
A 2006 03 7 7 5
A 2006 03 8 8 .
A 2006 03 . 9 1
A 2006 06 5 . .
A 2006 06 8 7 4
A 2006 06 . 4 3
A 2006 09 . . 2
A 2006 09 9 7 1
A 2006 09 . . 5
A 2007 03 . 7 .
A 2007 03 . 7 8
A 2007 03 . 7 .
A 2007 06 . 3 2
A 2007 06 . 7 6
A 2007 06 . 8 .
B 2006 03 . 2 1
B 2006 03 12 7 .
B 2006 03 10 . .
B 2007 03 9 1 2
B 2007 03 1 . .
B 2007 03 . 3 4
B 2007 03 . . .
B 2007 03 . 3 4
;
run;

[simonae]

Hi @ErikLund_Jensen,

Thank you very much for your reply. Below is an example data step I put together as a simplified version of the actual data I am working with. Is it helpful? (I think the start date variable should be numeric in order to accomplish what I need, but I am not sure).

data want;
input ID start_date $ jan2016 feb2016 mar2016 apr2016;
cards;
1 Jul2015 2 6 1 9
2 Aug2015 6 4 3 6
3 Oct2015 8 2 5 5
;
run;

[ErikLund_Jensen · Accepted solution]

Hi @simonae 

It's better, but it would be even better, if I had a sample with all months. I expanded it to 12 months in order to see it work over 2 half year intervals. The following solution demonstrates how it can be done with arrays. I will be happy to explain the code, if anything seems incomprehensible.

data have;
input ID start_date $ jan2016 feb2016 mar2016 apr2016 may2016 jun2016 jul2016 aug2016 sep2016 oct2016 nov2016 dec2016;
cards;
1 Jul2015 1 2 3 4 5 6 7 8 9 10 11 12
2 Aug2015 1 2 3 4 5 6 7 8 9 10 11 12
3 Oct2015 1 2 3 4 5 6 7 8 9 10 11 12
;
run;

data want (drop=IDstart Liststart offset i j); set have;
	array mlist jan2016 feb2016 mar2016 apr2016 may2016 jun2016 jul2016 aug2016 sep2016 oct2016 nov2016 dec2016;
	array halfyearlist mo6 m012 m018 mo24; 

	* Set first day of first month in list as a SAS date value;
	retain Liststart;
	if _N_ = 1 then Liststart = input('01'||vname(mlist{1}),date9.);

	* Set first day of start_date as a SAS date value;
	IDstart = input('01'||start_date,date9.);

	* compute offset (start_date relative to first month in list;
	offset = intck('month',IDstart,Liststart); 

	* loop over output variables - compute month list array index and transfer value;
	do i = 1 to 4;
		j = (i*6) - offset + 1;
		if 0 < j <= dim(mlist)> 0 then halfyearlist{i} = mlist{j};
	end;
run;

[simonae]

Thank you so much, @ErikLund_Jensen !! I tried it on my full data set and it works beautifully.

I appreciate all of your help!