turn on suggestions

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

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Do Loop over the same observation

Topic Options

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-11-2015 01:13 PM

Hello!

I'm trying to make this bit of code more robust:

if 99 le days le 182 then visit='6 month'; | |

else if days le ceil(((9*30.5)+14)) then visit='9 month'; | |

else if days le ceil(((12*30.5)+14)) then visit='12 month'; | |

else if days le ceil(((15*30.5)+14)) then visit='15 month'; | |

else if days le ceil(((18*30.5)+14)) then visit='18 month'; | |

else if days le ceil(((21*30.5)+14)) then visit='21 month'; | |

else if days le ceil(((24*30.5)+14)) then visit='24 month'; | |

else if days le ceil(((27*30.5)+14)) then visit='27 month'; | |

else if days le ceil(((30*30.5)+14)) then visit='30 month'; | |

else if days le ceil(((33*30.5)+14)) then visit='33 month'; | |

else if days le ceil(((36*30.5)+14)) then visit='36 month'; | |

else if days le ceil(((39*30.5)+14)) then visit='39 month'; | |

else if days le ceil(((42*30.5)+14)) then visit='42 month'; | |

else if days le ceil(((45*30.5)+14)) then visit='45 month'; |

And have been able to somewhat make it more robust using this method:

do i=9 by 3 until(last.id);

set origvisits;

days = VISITDTC -trtstdt;

label days='Number of days since original ib1001 start';

if 99 le days le 182 then visit='6 month';

by id ;

%let n=i;

if days le ceil(((i*30.5)+14)) then visit=strip(&n||' month');

else if days le ceil((((3+i)*30.5)+14)) then visit=strip(&n+3||' month');

else if days le ceil((((6+i)*30.5)+14)) then visit=strip(&n+6||' month');

else if days le ceil((((9+i)*30.5)+14)) then visit=strip(&n+9||' month');

else if days le ceil((((12+i)*30.5)+14)) then visit=strip(&n+12||' month');

output;

end;

But its still is not robust enough.

What I've constructed above is each observation is being read in with i=9 and increasing by 3 until last.id. And then i increments sequentially for each observation within that by group (id, in this case)

What I really want SAS to do is read one observation at a time and keep trying values of i (the index of the do loop) until the condition if days le ceil(((i*30.5)+14)) is true, so that I can assign a nominal visit date.

For example, suppose the condition days le (9*30.5)+14 is not true, I want SAS to stay with that same observation and try (12*30.5)+14, if that condition is not true then try (15*30.5)+14 until the **days le (i*30.5)+14** condition is true. Then move on to the next observation in the dataset, with i being reset back to 9.

I hope this makes sense, I know what I want SAS to do...but can't quite articulate it.

Any help or guidance would be appreciated

Accepted Solutions

Solution

05-11-2015
03:00 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-11-2015 03:00 PM

Your initial method is inefficient - it will work but not easily scalable or understandable as you're seeing.

Does the following get you close? You probably still need your first if condition to deal with the less than 9 months interval.

months=(days-14)/30.5;

months3=floor(months/3)*3;

month_category = put(months3, 2.)||" month";

All Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-11-2015 01:41 PM

The way to make this more robust is to consider the two dates involved in the calculation of **days** and to use SAS date handling functions **INTNX** and **INTCK** in the determination of the **visit** value.

PG

PG

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-11-2015 02:09 PM

The second date in the comparison is being derived so that I can assign a nominal visit. The number of days between visit date and treatment start date (days = VISITDTC -trtstdt) is a fixed value for each observation. Now what I need to do is derive a nominal visit for that value of days, based on what is specified in our protocol (months*30.5)+14.

Lets assume days= 452. So I start the loop with i=9. (9*30.5)+14=289. Since 452 is not less than 289, a nominal visit wont be assigned, the loop moves on to the next observation. Instead, what I want to happen is now with that same observation have it evaluate the condition for i=12 ---> (12*30.5)+14=380. 452 is still not less than 380, so lets move on to i=15. (15*30.5)+14 =472. Bingo! 452 is less than 472. This visit is assigned a nominal visit of 15 months.

Move on to the next observation, and start the same process with i=9 until the condition days le (i*30.5)+14 is met.

Hope that clarifies what I am looking to accomplish

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-11-2015 02:12 PM

You are mixing SAS DATA step processing MACRO language (%LET stmt) which is resolved typically at DATA / PROC step compilation. Suggest looking at using PROC FORMAT to assign/derive VISIT based on pre-defined value ranges.

Also, consider opportunity to start out your post by explaining what it is you intend/desire to accomplish in most basic terms -- as well show an abbreviated data-sample (both INPUT side and OUTPUT).

Scott Barry

SBBWorks, Inc.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-11-2015 02:20 PM

Thanks for the suggestion.

The PROC FORMAT won't be useful in this case, since I would have to continually add date values, etc.

What I want is to automate this process, by the example stated above. I just want to loop values of i for one observation at a time until a condition is satisfied, then move on to the next observation and start the loop over (in this case, starting over means starting at i=9) and continue to loop through values of i until the condition days le (i*30.5)+14 is met.

Do you know if there is a way to accomplish this? Basically telling SAS to read in an observation, hold it there, try different values of i (starting at 9, incrementing by 3) until days le (i*30.5)+14 is true, then move on to the obs and start the process over.

Thanks!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-11-2015 02:31 PM

Well, it's not exactly clear what you mean by "robust" here. Doesn't cover a wide enough date range? Sometimes gives the wrong answer?

At any rate, you may not need a loop at all. You could start here:

if days ge 183 then n_months = (days - 14) / 30.5;

Tweak the formula if that would be appropriate, then apply functions such as CEIL, ROUND, etc. as needed to generate VISIT.

I do agree with PGStats that it might be more accurate to go back to the variables used to compute DAYS as your starting point.

Good luck.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-11-2015 02:45 PM

Well, I guess what I mean by robust is a solution that is not so data-driven.

A clinical trial can go on for years and years (which can produce a lot of three month intervals). I want to be able to capture all three month intervals and assign a nominal visit. We have a "formula" for calculating a nominal visit spelled out in our protocol; in this case its (months*30.5)+14.

All I want to do is "plug-n-chug" different values of i (starting at 9, increment by 3) for each individual observation (row of data) in my data set until the condition **days le (i*30.5) +14 **is satisfied. Then move on to the next observation.

In my partial solution, I came up with this:

do i=9 by 3 until(last.id);

set origvisits;

days = VISITDTC -trtstdt;

label days='Number of days since original ib1001 start';

if 99 le days le 182 then visit='6 month';

by id ;

%let n=i;

if days le ceil(((i*30.5)+14)) then visit=strip(&n||' month');

else if days le ceil((((3+i)*30.5)+14)) then visit=strip(&n+3||' month');

else if days le ceil((((6+i)*30.5)+14)) then visit=strip(&n+6||' month');

else if days le ceil((((9+i)*30.5)+14)) then visit=strip(&n+9||' month');

else if days le ceil((((12+i)*30.5)+14)) then visit=strip(&n+12||' month');

output;

end;

Which could potentially not cover all the values I need, that is I can "run out" of nominal visits. You see I'm kind of forcing SAS to go to the next i (by adding 3) if the condition is not met.

Is there a way where I can tell SAS to do something of the effect

if days le ceil(((i*30.5)+14)) then visit=strip(&n||' month');

else if try the next value of i and perform the same calculation as above until you get an answer

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-11-2015 02:48 PM

Okay,so you need a RETAIN SAS variable to use as a base-increment for each observation you iterate.

Suggest some self-initiated desk-checking using PUTLOG ">LBL-nnn" / _ALL_; so that you can decipher DATA step iterations along the path of your program -- the "nnn" you would identify so that you can identify "I am here".....

And so check your DO statement syntax, for example: DO I=9 TO 3 BY -1 UNTIL(<condition);

Scott Barry

SBBWorks, Inc.

Solution

05-11-2015
03:00 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-11-2015 03:00 PM

Your initial method is inefficient - it will work but not easily scalable or understandable as you're seeing.

Does the following get you close? You probably still need your first if condition to deal with the less than 9 months interval.

months=(days-14)/30.5;

months3=floor(months/3)*3;

month_category = put(months3, 2.)||" month";

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-11-2015 05:07 PM

Ha! Wow, so much simpler than I ever thought. Elegant and simple, exactly what I was looking for!

Thanks!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-12-2015 03:17 PM

As much as I love this solution, looks like I need to do something as I mentioned before so that it will comply with our protocol and statistical analysis plan. Is there a way to iterate values of i one observation at a time...like how I was attempting to explain earlier?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-12-2015 03:37 PM

From your example and as was conveyed, you cannot mix macro language within DATA step iteration, so you will need to increment a second SAS DATA step variable (maybe call it n, used as in N+1 and then make use of that variable when you assign VISIT although using a PUT(N,3.) combined with your other sub-character value assignments.

Scott Barry

SBBWorks, Inc.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-12-2015 03:41 PM

Recommend you pair down your DATA step logic, perhaps using a stable instream DATALINES; data sample - not your input. And make use of DATA step PUTLOG _ALL_; to observe SAS behavior when in very controlled process. This will provide you some insight as to a possible technique / variation, and then implement the simple technique to adapt to your desired input / output data scenario.

It will be to your benefit to leverage the SAS processing behavior experience with various DIAGNOSTIC techniques, such as OPTIONS MGEN SGEN MLOGIC; and PUTLOG as suggested with your own SAS code-piece -- as much (or more for experience gaining) as it would be to pose questions / wait / reply / then iterate....