topic Re: Using arrays to identify specific column in a dataset in SAS Programming

Using arrays to identify specific column in a dataset

simonae — Thu, 06 Jun 2019 00:03:03 GMT

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!

Re: Using arrays to identify specific column in a dataset

ErikLund_Jensen — Thu, 06 Jun 2019 16:44:39 GMT

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;

Re: Using arrays to identify specific column in a dataset

simonae — Thu, 06 Jun 2019 18:37:22 GMT

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;

Re: Using arrays to identify specific column in a dataset

ErikLund_Jensen — Thu, 06 Jun 2019 19:33:21 GMT

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;

Re: Using arrays to identify specific column in a dataset

simonae — Thu, 06 Jun 2019 21:00:59 GMT

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

I appreciate all of your help!