topic Re: Copy range of data from one column to another with different starting columns in SAS Programming

Copy range of data from one column to another with different starting columns

rox26 — Thu, 23 May 2024 15:30:24 GMT

I'm conducting an analysis on SAS 9.4 with health insurance enrollment data and want to understand the enrollment patterns before and after a diagnosis. I have monthly data for several years which I want to select 24 rolling months based around a diagnosis date. I want to copy over the select months into new columns based on the start month range. I have a dummy example below where month1-month7 are the enrollment months 1 through 7 and start_month is the number of the start month and end _month is the number of the end month of the range. I want to copy the data into the "next" variables for a range of 4. Adding "output" into the code doesn't solve the issue. The desired output at the end of this code. Thank you!

*table creation for 2 examples;
data try1;
input month1-month7 start_month end_month;
datalines;
1 2 3 4 5 6 7 2 4
1 2 3 4 5 6 7 4 7
;
run;
proc print data=try1;
run;

*This straight copies the data into the "next" variables, not based on start/end range;
data try2;
set try1;
array month month1-month7; /*enrollment months*/
array next next1-next4; /*want to copy over select months here*/

do i=1 to 4; /*interested in range of 4 months*/
next{i}=month{i}; /*this straight copies the data*/
end;
run;
proc print data=try2;
run;

*HERE IS WHERE I AM STUCK;
*Trying to copy the select months based on the start and end range;
*This code below only copies the last select month, which is wrong;
data try3;
set try1;
array month month1-month7; /*enrollment months*/
array next next1-next4; /*want to copy over select months here*/
do k=start_month to end_month; /*start month and end month for each person*/
do i=1 to 4; /*interested in range of 4 months*/
next{i}=month{k}; /*this code is incorrect, it just copies the last month */
end;
end;
run;
proc print data=try3;
run;

/*This is the desired output*/

data want;
input month1-month7 start_month end_month next1-next4;
datalines;
1 2 3 4 5 6 7 2 4 2 3 4 .
1 2 3 4 5 6 7 4 7 4 5 6 7
;
run;
proc print data=want;
run;

Re: Copy range of data from one column to another with different starting columns

mkeintz — Thu, 23 May 2024 16:29:53 GMT

And what if start_month through end_month designate a span more than 4 months? Do you still want only next1-next4? In other words, what is the longest range you want to accommodate for the NEXT variables?

But if all you want are next1-next4, and start_month through end_month is never more than 4 months, then:

data try1;
input month1-month7 start_month end_month;
datalines;
1 2 3 4 5 6 7 2 4
1 2 3 4 5 6 7 4 7
run;

data want (drop=n);
  set try1;
  array nxt {4} next1-next4;

  do n=1 to  1 + end_month - start_month;
    nxt{n} = start_month-1+n;
  end;
run;

Notice there is no array in this code for the MONTH variables. That's because the VALUE of each month exactly equals its array POSITION. But if MONTH1-MONTH7 have other values, then:

data want (drop=n);
  set try1;
  array mon {*} month1-month7;
  array nxt {4} next1-next4;

  do n=1 to  1 + end_month - start_month;
    nxt{n} = mon{start_month-1+n};
  end;
run;

Re: Copy range of data from one column to another with different starting columns

ballardw — Thu, 23 May 2024 16:36:43 GMT

I don't see anything resembling a diagnosis date or enough data to do anything resembling 24 months around that.

It is very likely that the process is much cleaner with actual date values but I'm not sure that I understand how this step even relates to the desription.

Re: Copy range of data from one column to another with different starting columns

rox26 — Thu, 23 May 2024 18:20:43 GMT

The latter works perfectly with my real data, thank you SO much!

Re: Copy range of data from one column to another with different starting columns

rox26 — Thu, 23 May 2024 18:22:09 GMT

I thought using a simple dataset would be easier but I also didn't want to leave out the background explanation. I see how this is confusing. I will take this into consideration next time, thanks.