topic Re: How to Control Starting Point of Do Loop Based on Results of Other Field? in SAS Programming

How to Control Starting Point of Do Loop Based on Results of Other Field?

shellp55 — Wed, 17 Apr 2019 17:26:24 GMT

Hello

I have data formatted as wide and I want to loop through to find a code from the same range twice. So I want the second do loop to start 1 after finding the other.

For instance, if the first code is found at occurrence 3 of 25 I want the next do loop to start at 4 and go to 25. But they won't all be in the same occurrence so I want to be able to use the results of the first occurrence dynamically. Thanks.

Re: How to Control Starting Point of Do Loop Based on Results of Other Field?

Tom — Wed, 17 Apr 2019 17:28:28 GMT

Just use a variable for the lower end of the DO loop.

do second_loop= first_location +1 to dim(array_name);

Re: How to Control Starting Point of Do Loop Based on Results of Other Field?

Reeza — Wed, 17 Apr 2019 18:00:32 GMT

How many diags are you looking at? You could use WHICHC/WHICHN to find the first index easily and then use that number as your loop counter as Tom suggests.

Alternatively, doing this in a long format data set is very trivial with BY group processing.

Re: How to Control Starting Point of Do Loop Based on Results of Other Field?

shellp55 — Wed, 17 Apr 2019 18:08:41 GMT

Thanks Tom but I tried what you suggested and it didn't work. Below is some test data and it doesn't work but you can see where I want to start where the first occurrence was found +1.

Thanks Reeza, I'd like to see if there is a solution I can use with the data wide but certainly if I have to make it long then I will.

In the below example, charts 111111, 33333, 55555, and 66666 all have two codes from the same range and is what I would want picked up in the criteria of crit1 gt 0 and crit2 gt 0.

data test_grp;
input @1  chart $5.
	  @6  regn  $3.
	  @9  dcde1  $3. 
	  @12 dcde2  $3.
	  @15 dcde3  $3.
	  @18 dcde4  $3.	 
;
cards;
11111001Y60J90B55Y84
22222002J90B89C40A40
33333003Y60Y68B94J90
44444004C93C82J44Y60
55555005Y60Y89L98M42
66666006Y60Y54A42G90
77777007B92F04C44C83
run; 

data test;
set test_grp;

array dx(4) dcde1-dcde4;

crit1=0;
crit2=0;

do i = 1 to 4;
if "Y60" <= substr(dx[i],1,3) <= "Y84" then crit1=1;
if "Y60" <= substr(dx[i],1,3) <= "Y84" then dx1_occur=i;
end;

do j = (dx1_occur+1) to 4;
if "Y60" <= substr(dx[j],1,3) <= "Y84" then crit1=1;
end;


if crit1 gt 0 and crit2 gt 0 then output;

run;

Re: How to Control Starting Point of Do Loop Based on Results of Other Field?

shellp55 — Wed, 17 Apr 2019 18:10:22 GMT

Noticed an error in the code for j do loop, should be crit2=1. Note also that the code for the do loop starting point doesn't work, just leaving it to show what I want to be able to do. Thanks.

Re: How to Control Starting Point of Do Loop Based on Results of Other Field?

Reeza — Wed, 17 Apr 2019 18:18:09 GMT

data test;
set test_grp;

array dx(4) dcde1-dcde4;

crit1=0;
crit2=0;

do i = 1 to 4 while(crit1=0);
if "Y60" <= substr(dx[i],1,3) <= "Y84" then crit1=1;
end;

do j = i to 4;
if "Y60" <= substr(dx[j],1,3) <= "Y84" then crit2=1;
end;


*if crit1 gt 0 and crit2 gt 0 then output;

run;

I switched it to a WHILE loop, so you can use I directly in the next loop. It automatically increments by 1, so you don't need to do that step either. I commented out the last line to check results.

Re: How to Control Starting Point of Do Loop Based on Results of Other Field?

Astounding — Wed, 17 Apr 2019 18:23:30 GMT

Given that you have explained a little more, here is a shorter version of what you could do:

data test;
set test_grp;

array dx(4) dcde1-dcde4;

crit1=0;
crit2=0;

do i = 1 to 4;
   if crit1=1 and "Y60" <=: dx[i] <=: "Y84" then crit2=1;
   if "Y60" <=: dx[i] <=: "Y84" then crit1=1;
end;

if crit1 gt 0 and crit2 gt 0 then output;

run;

Notice that you don't need SUBSTR as long as you add the colon to the end of the comparison ... a significant time-saver.

I'm also trying to understand why you need two flags instead of one:

data test;
set test_grp;

array dx(4) dcde1-dcde4;

crit=0;

do i = 1 to 4;
   if "Y60" <=: dx[i] <=: "Y84" then crit + 1;
end;

if crit gt 1 then output;

run;

Re: How to Control Starting Point of Do Loop Based on Results of Other Field?

Tom — Wed, 17 Apr 2019 18:23:22 GMT

One of the nice features of DO loops is you can combine iterative loops with WHILE or UNTIL condition testing.

data test;
  set test_grp;
  array dx dcde1-dcde4;
  crit1=0;
  crit2=0;
  dx1_occur=0;

  do i = 1 to dim(dx) until (crit1);
    crit1 = "Y60" <=: dx[i] <=: "Y84";
  end;
  if crit1 then dx1_occur=i;

  do i = (dx1_occur+1) to dim(dx) until (crit2);
    crit2 = "Y60" <=: dx[i] <=: "Y84";
  end;
  drop i;
  if crit1 and crit2 then output;
run;

Re: How to Control Starting Point of Do Loop Based on Results of Other Field?

Reeza — Wed, 17 Apr 2019 18:24:13 GMT

Just a quick FYI - character comparisons are tricky but I think it will work in this situation. It may not work for all though, so ensure that you do thorough testing with the character comparisons.

Re: How to Control Starting Point of Do Loop Based on Results of Other Field?

shellp55 — Wed, 17 Apr 2019 18:25:09 GMT

Thanks Reeza but that also picks up the cases that only have 1 of the codes in the range, not two codes.

Thanks Astounding but crit1 isn't greater than zero unless I run the code to first find out that is the case i.e. nowhere else is crit1 mentioned to then be used in the do loop..or am I missing something?

Shelley

Re: How to Control Starting Point of Do Loop Based on Results of Other Field?

Reeza — Wed, 17 Apr 2019 18:26:14 GMT

@shellp55, only because I commented out the last criteria. Otherwise pretty sure it works. You only want record 1 and 3, correct?

Re: How to Control Starting Point of Do Loop Based on Results of Other Field?

Astounding — Wed, 17 Apr 2019 18:27:01 GMT

Yes, you're missing the logic of the DO loop.

CRIT1 can get set within the loop, and CRIT2 only gets set after CRIT1 has already been set.

Give it a try. And especially notice both possible programs.

Re: How to Control Starting Point of Do Loop Based on Results of Other Field?

Tom — Wed, 17 Apr 2019 18:27:44 GMT

If you just want to find cases with 2 or more codes in the same group then keep count of how many you find.

data test;
  set test_grp;
  array dx dcde1-dcde4;
  found=0;
  do i = 1 to dim(dx) until (found>=2);
    found +  ("Y60" <=: dx[i] <=: "Y84");
  end;
  if found >= 2 then output;
  drop i;
run;

Re: How to Control Starting Point of Do Loop Based on Results of Other Field?

novinosrin — Wed, 17 Apr 2019 18:27:53 GMT


data test_grp;
input @1  chart $5.
	  @6  regn  $3.
	  @9  dcde1  $3. 
	  @12 dcde2  $3.
	  @15 dcde3  $3.
	  @18 dcde4  $3.	 
;
cards;
11111001Y60J90B55Y84
22222002J90B89C40A40
33333003Y60Y68B94J90
44444004C93C82J44Y60
55555005Y60Y89L98M42
66666006Y60Y54A42G90
77777007B92F04C44C83
run; 
data test1;
set test_grp;

array dx(4) dcde1-dcde4;

crit1=0;
crit2=0;

do i = 1 to 4;
if "Y60" <= substr(dx[i],1,3) <= "Y84" then crit1=1;
if "Y60" <= substr(dx[i],1,3) <= "Y84" then do;
dx1_occur=i;
leave;
end;
end;
if dx1_occur>. then  do j = (dx1_occur+1) to 4;
if "Y60" <= substr(dx[j],1,3) <= "Y84" then crit2=1;
end;
if crit1 and crit2 then output;
run;
proc print noobs;run;

chart	regn	dcde1	dcde2	dcde3	dcde4	crit1	crit2	i	dx1_occur	j
11111	001	Y60	J90	B55	Y84	1	1	1	1	5
33333	003	Y60	Y68	B94	J90	1	1	1	1	5

Re: How to Control Starting Point of Do Loop Based on Results of Other Field?

shellp55 — Wed, 17 Apr 2019 18:32:54 GMT

This works Tom, thanks so much! I firstly indicated that charts 55555 and 66666 should also be flagged but I noticed when they didn't show up with your code it is because two of the codes are out of range within those charts so we are good.

Thanks again to everyone who offered assistance, greatly appreciated!

Re: How to Control Starting Point of Do Loop Based on Results of Other Field?

shellp55 — Wed, 17 Apr 2019 18:35:39 GMT

Wow, so many people answering on this site, thanks very much Novinosrin!!