Find first month in which flag occurs, across columns

Reply
Super Contributor
Posts: 418

Find first month in which flag occurs, across columns

Hello everyone. I have a dataset that has thousands of loans (down the rows) by months (across columns). It is basically a giant matrix of yes /no values (1,0 in format).

Does anyone know a way to find the FIRST column (month) in which a yes flag occurs?  Please see the following excel file example with the final column named "desired answer" for an example of the input and output that I am looking for.

If there is a way to do this without transposing the data it would be greatly appreciated, since the data is extremely large.

Please let me know if anyone has any questions on this request!

Super User
Posts: 19,877

Re: Find first month in which flag occurs, across columns

Posted in reply to Anotherdream

Your example has the first as 0/1/2 rather than 0/1, is that the actual case as your original question states 0/1?

If it's only 0/1 look at the whichn function across the months.

SAS(R) 9.2 Language Reference: Dictionary, Fourth Edition

Super User
Posts: 10,046

Re: Find first month in which flag occurs, across columns

Posted in reply to Anotherdream
proc import datafile='c:\temp\monte carlo example.xls' out=have dbms=excel replace;run;
data want(drop=i);
 set have;
 array x{*} month:;
 want=0;
 do i=1 to dim(x);
  if x{i} then do;want=i;leave;end;
 end;
run;


Xia Keshan

Ask a Question
Discussion stats
  • 2 replies
  • 198 views
  • 0 likes
  • 3 in conversation