BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
EmilieKL
Calcite | Level 5

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:

IDy_1201y_1202y_1203y_1204y_1205y_1206begin
1100100100200200200y_1203
2300100100300200200y_1201

 

Goal example (only first 6 weeks shown here): 

IDweek1week2week3week4week5week6
1100200200200..
2300100100300200200

Thanks for helping!

1 ACCEPTED SOLUTION

Accepted Solutions
yabwon
Onyx | Level 15

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



View solution in original post

5 REPLIES 5
Reeza
Super User
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
Onyx | Level 15

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



EmilieKL
Calcite | Level 5

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
Onyx | Level 15

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



PGStats
Opal | Level 21

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;

PGStats_0-1647362928309.png

 

PG

sas-innovate-2024.png

Available on demand!

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

 

Register now!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 509 views
  • 3 likes
  • 4 in conversation