Array with value of variable as start

[EmilieKL]

Hi, 

I have weekly data, with each week of every year having a variable (first six week of 2012 shown in example, however my dataset covers multiple years). 

I've made a variable "begin" which indicates when each individual was assigned to treatment. 

My goal is to create a dataset showing the first 50 weeks of treatment for each individual.

Week1 should equal y_1203 (=100) for ID=1, y_1201(=300) for ID=2 and so on.

I've have tried multiple variations of arrays, however I cannot seem to get it right.

Is there any way to begin the array at the variable equal to the value of "begin" for each ID?

Example of data:

ID	y_1201	y_1202	y_1203	y_1204	y_1205	y_1206	begin
1	100	100	100	200	200	200	y_1203
2	300	100	100	300	200	200	y_1201

Goal example (only first 6 weeks shown here): 

ID	week1	week2	week3	week4	week5	week6
1	100	200	200	200	.	.
2	300	100	100	300	200	200

Thanks for helping!

[Reeza]

You can if you index your array not using 1,2, 3 and have a manual index set up instead. Then you can parse the number and start your array there.

However, a better and more dynamic solution is to have your data in a long format rather than a wide format, then check when begin matches your ID column. From there start counting weeks. Then if you really want a wide format, you would transpose it.

[yabwon · Accepted solution]

Try this:

data have;
input ID	y_1201	y_1202	y_1203	y_1204	y_1205	y_1206	begin $;
cards;
1	100	100	100	200	200	200	y_1203
2	300	100	100	300	200	200	y_1201
;
run;
 
data want;
  set have;
  array y(i) y_:;
  array week[6];

  output = 0;
  do over y;
    if vname(y) = begin then output = 1;    
    if 0 < output <= dim(week) then 
      do;
        week[output] = y;
        output + 1;
      end;
  end;
  output;
keep id week:;
run;
proc print;
run;

Bart

[EmilieKL]

Thank you very much, this works perfectly and gets the exact result I was looking for!

Do you reckon it would be possible to get the 6 week prior to the assignment of treatment as well? 

(e.g. variables week_6-week_1)

[yabwon]

Maybe like this:

data have;
input ID	begin $;
array y[*] y_1201-y_1252 (1:52);
cards;
1	y_1203
2	y_1212
3 y_1248
;
run;
 
data want;
  set have;
  array y[*] y_:;
  array week[1:12] week_6-week_1 week1-week6;

  output = 0; j = 13;
  do i = lbound(y) to hbound(y);
    if vname(y[i]) = begin then 
      do;
        output = 1;
        j = i - 6;
        leave; 
      end;
  end;

  if output then 
    do output = lbound(week) to hbound(week);
        if lbound(y) <= j <= hbound(y) then week[output] = y[j];
        j + 1;
      end;

  output;
keep id week:;
run;
proc print;
run;

Bart

[PGStats]

You could do:

data have;
input ID    y_1201  y_1202  y_1203  y_1204  y_1205  y_1206  begin $;
datalines;
1 100 100 100 200 200 200 y_1203
2 300 100 100 300 200 200 y_1201
;

data want;

array x week1-week50;

set have;
array y y_:;

w = 1;
start = 0;
do i = 1 to dim(y) until (w > dim(x));
    if vname(y{i}) = begin then start = 1;
    if start then do;
        x{w} = y{i};
        w = w + 1;
        end;
    end;
keep id week:;
run;
    
proc print data=want noobs; var id week1-week6; run;