array with dates

[deleted_user]

I have 3 columns: station, collection_date (as mm/yyyy), and wq_msmt. Each station was visited monthly for six years and and a wq_msmt was made. I want to use an array to have a column for each mm/yyyy, thus 72 columns, with the value under that column being that month's wq_msmt at a particular station, so there is one more column for station giving a total of 73 columns.

In essence, I want to go from a long, narrow data structure to a short, wide data structure. But, I think having collection date as a variable is throwing off my program as I get the error: ERROR: Array subscript out of range at line 114 column 5

Do arrays handle dates?

My code is below.
Thanks,
Mike


data n_ammonia_array;
array mon[72] mon1-mon72;
retain mon1-mon72;
set n_ammonia_total_org3;
by FK_STATION;
if first.FK_STATION then do i=1 to 72;
mon=.;
end;
mon[c_date]=binary_resid;
if last.FK_STATION then output;
keep FK_STATION mon1-mon72;
run;

[deleted_user]

Collection date as a variable is not your problem.

In your sample you refer to "c_date"! Consistent naming would help with the diagnosis.

I presume you know what SAS dates are, and therefore, if collection date is a real date which has a format of "mm/yyyy" associated with it, then your array will be in range for 1 Jan 1960 until the early part of March in the same year. After that, the date value is higher than 72 and the subscript will be out of range.

A solution is to count the number of months and add the month number as a cohort to the data set and use this for the array definition. Bear in mind that if you calculate the cohort using the IntNx() function and use the first month as your from date, then your first month will be 0 and not 1. 0 is also out of range, so you need to increment from one month prior to the first observation month.

Assuming you are analysing a six year period from Jan 2000 to Dec 2005, the following may help:

COHORT = IntNx( 'Month', '30Dec1999'd, COLLECTION_DATE);

I must ask though, why are you doing this? Most SAS statistical procedures prefer vertical structures, and use By statements to handle grouping.