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...
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.
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
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;
lol. appreciate it. I can follow this code, i will test it soon. col=i does not appear correct, assigning it an integer?
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.
works great. i appreciate the help
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.