topic Re: Some do loop/array help in SAS Programming

Some do loop/array help

eawh100 — Tue, 12 Dec 2023 19:13:25 GMT

Hello,

I need some help with an array or do loop.

Here is an example of the data I have:

id	location	yes_no
a	1	0
a	2	0
a	3	0
a	4	0
a	5	0
b	1	0
b	2	1
b	3	2
b	4	1
b	5	0
c	1	0
c	2	0
c	3	0
c	4	0
c	5	0
d	1	0
d	2	0
d	3	0
d	4	0
d	5	0
e	1	0
e	2	2
e	3	0
e	4	0
e	5	0

What I want is a new column that outputs something only if all rows for the same id have 0's in the yes_no column. so if by the last row for a particular id, there have only been zeros, I need to flag that id.

So the new data would look something like this:

id	location	yes_no	check
a	1	0
a	2	0
a	3	0
a	4	0
a	5	0	yes
b	1	0
b	2	1
b	3	2
b	4	1
b	5	0
c	1	0
c	2	0
c	3	0
c	4	0
c	5	0	yes
d	1	0
d	2	0
d	3	0
d	4	0
d	5	0	yes
e	1	0
e	2	2
e	3	0
e	4	0
e	5	0

Re: Some do loop/array help

Quentin — Tue, 12 Dec 2023 19:22:34 GMT

Can you show the code you have tried? Have you thought about using BY-group processing in a DATA step? Or perhaps PROC SQL? Showing the code you tried, and describing why it's not working (error messages? wrong result?) will help people help you.

Re: Some do loop/array help

PaigeMiller — Tue, 12 Dec 2023 19:25:58 GMT

Although there are solutions that involve do-loops, they are not needed here. I can't possibly see how ARRAYs would be of use here.

proc summary data=have nway;
    class id;
    var yes_no;
    output out=_sums_ sum=sum_yes_no;
run;
data want;
    merge have _sums_(keep=id sum_yes_no);
    by id;
    if sum_yes_no=0 and last.id then check=1;
run;

Note: this method can fail if the variable yes_no can take on negative values.

Re: Some do loop/array help

A_Kh — Tue, 12 Dec 2023 19:27:41 GMT

data have;
	input id $	location	yes_no;
	cards;
a	1	0
a	2	0
a	3	0
a	4	0
a	5	0
b	1	0
b	2	1
b	3	2
b	4	1
b	5	0
c	1	0
c	2	0
c	3	0
c	4	0
c	5	0
d	1	0
d	2	0
d	3	0
d	4	0
d	5	0
e	1	0
e	2	2
e	3	0
e	4	0
e	5	0
;

data want;
	set have;
	by id;
	retain temp;
	if first.id then temp=yes_no;
	else temp+yes_no;
	if last.id and temp=0 then check='Yes';
	drop temp; 
proc print;run;

Re: Some do loop/array help

Kurt_Bremser — Tue, 12 Dec 2023 19:47:37 GMT

Single DATA step method:

data want;
set have;
by id;
retain flag;
if first.id then flag = 1;
if yes_no ne 0 then flag = 0;
if last.id and flag then check = "yes";
drop flag;
run;

Re: Some do loop/array help

eawh100 — Tue, 12 Dec 2023 21:20:31 GMT

Thanks, this works too!

Re: Some do loop/array help

eawh100 — Tue, 12 Dec 2023 21:20:47 GMT

thanks, this worked!