topic Re: Do Loop over the same observation in SAS Programming

Do Loop over the same observation

sahotah — Mon, 11 May 2015 17:13:45 GMT

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

Re: Do Loop over the same observation

PGStats — Mon, 11 May 2015 17:41:11 GMT

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.

Re: Do Loop over the same observation

sahotah — Mon, 11 May 2015 18:09:40 GMT

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

Re: Do Loop over the same observation

sbb — Mon, 11 May 2015 18:12:02 GMT

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.

Re: Do Loop over the same observation

sahotah — Mon, 11 May 2015 18:20:07 GMT

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!

Re: Do Loop over the same observation

Astounding — Mon, 11 May 2015 18:31:03 GMT

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.

Re: Do Loop over the same observation

sahotah — Mon, 11 May 2015 18:45:56 GMT

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

Re: Do Loop over the same observation

sbb — Mon, 11 May 2015 18:48:32 GMT

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.

Re: Do Loop over the same observation

Reeza — Mon, 11 May 2015 19:00:25 GMT

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

Re: Do Loop over the same observation

sahotah — Mon, 11 May 2015 21:07:29 GMT

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

Thanks!

Re: Do Loop over the same observation

sahotah — Tue, 12 May 2015 19:17:47 GMT

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?

Re: Do Loop over the same observation

sbb — Tue, 12 May 2015 19:37:18 GMT

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.

Re: Do Loop over the same observation

sbb — Tue, 12 May 2015 19:41:05 GMT

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....