Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- Programming
- /
- Array with value of variable as start

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

🔒 This topic is **solved** and **locked**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 03-15-2022 11:35 AM
(508 views)

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!

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

_______________

**Polish SAS Users Group**: www.polsug.com and communities.sas.com/polsug

"**SAS Packages: the way to share**" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.

Hands-on-Workshop: "**Share your code with SAS Packages**"

"**My First SAS Package: A How-To**" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three

SAS Documentation

"

Hands-on-Workshop: "

"

SAS Ballot Ideas: one: SPF in SAS, two, and three

SAS Documentation

5 REPLIES 5

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

_______________

**Polish SAS Users Group**: www.polsug.com and communities.sas.com/polsug

"**SAS Packages: the way to share**" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.

Hands-on-Workshop: "**Share your code with SAS Packages**"

"**My First SAS Package: A How-To**" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three

SAS Documentation

"

Hands-on-Workshop: "

"

SAS Ballot Ideas: one: SPF in SAS, two, and three

SAS Documentation

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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)

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

_______________

**Polish SAS Users Group**: www.polsug.com and communities.sas.com/polsug

"**SAS Packages: the way to share**" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.

Hands-on-Workshop: "**Share your code with SAS Packages**"

"**My First SAS Package: A How-To**" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three

SAS Documentation

"

Hands-on-Workshop: "

"

SAS Ballot Ideas: one: SPF in SAS, two, and three

SAS Documentation

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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;
```

PG

**Available on demand!**

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

How to Concatenate Values

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.