DATA Step, Macro, Functions and more

loop through columns and returning value

Reply
Occasional Contributor
Posts: 6

loop through columns and returning value

IDcol1col2col3col4col5col6col7col8col9col10
1012567891011
201235678910
305678910111213

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:

IDCol
12 (since Col4 - Col3 >1)
210the last column in case there are no gaps
30 (since Col2-Col1 >1)

please advise...

Super User
Posts: 19,770

Re: loop through columns and returning value

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

Occasional Contributor
Posts: 6

Re: loop through columns and returning value

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

Super User
Posts: 19,770

Re: loop through columns and returning value

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;

end;

run;

Occasional Contributor
Posts: 6

Re: loop through columns and returning value

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

Super User
Posts: 19,770

Re: loop through columns and returning value

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.

Occasional Contributor
Posts: 6

Re: loop through columns and returning value

works great. i appreciate the help

Ask a Question
Discussion stats
  • 6 replies
  • 304 views
  • 0 likes
  • 2 in conversation