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!
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;
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;
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;
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;
Thank you so much, @ErikLund_Jensen !! I tried it on my full data set and it works beautifully.
I appreciate all of your help!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.