topic Re: loop through columns and returning value in SAS Programming

loop through columns and returning value

chayo — Thu, 13 Jun 2013 20:47:22 GMT

ID	col1	col2	col3	col4	col5	col6	col7	col8	col9	col10
1	0	1	2	5	6	7	8	9	10	11
2	0	1	2	3	5	6	7	8	9	10
3	0	5	6	7	8	9	10	11	12	13

Hi- I'm new to this form, I have been looking into this for a long time with no success. I need to write a sas code that loop through all the columns and return ID and the Value from Col(x) where Col(x+1)-Col(x) > 1.

The output would be like this:

ID	Col
1	2	(since Col4 - Col3 >1)
2	10	the last column in case there are no gaps
3	0	(since Col2-Col1 >1)

please advise...

Re: loop through columns and returning value

Reeza — Thu, 13 Jun 2013 20:53:47 GMT

Create an array and loop through it using a break statement to stop at the first difference.

You'll want to start your loop at two and go from there.

SAS Learning Module: Working across variables

Re: loop through columns and returning value

chayo — Thu, 13 Jun 2013 20:55:36 GMT

thanks. I was able to write this using loops in vba. I'm new to SAS, that's why I'm struggling with the syntax. I will continue looking into it. thanks

Re: loop through columns and returning value

Reeza — Thu, 13 Jun 2013 21:00:40 GMT

Since you're from Microsoft land and I don't want you to hate SAS here's some untested code:

data have;

array col(10) col1-col10;

flag=0;

do i=2 to 10 while (flag ne 0);

if col-col[i-1]>1 then do;

flag=1;

col=i;

end;

run;

Re: loop through columns and returning value

chayo — Thu, 13 Jun 2013 21:08:48 GMT

lol. appreciate it. I can follow this code, i will test it soon. col=i does not appear correct, assigning it an integer?

Re: loop through columns and returning value

Reeza — Thu, 13 Jun 2013 21:12:39 GMT

i is the variable counter, so its an interger 1 to 10.

I just noticed that you're using VBA array counting though, 0 to 9 ish, so you may want to use i-1 instead...you can test it out.

Re: loop through columns and returning value

chayo — Thu, 13 Jun 2013 21:49:55 GMT

works great. i appreciate the help