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!
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
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
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)
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
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.