How to iterate over columns to find the first instance greater than a designated value

Accepted Solution Solved
Reply
Contributor
Posts: 20
Accepted Solution

How to iterate over columns to find the first instance greater than a designated value

Hi,

 

The data set that I am using has a ID column and then the next 120 columns contain the monthly data from every month for the last 10 years. For each ID, I need to be able to create a column that determines the first month that the value for that month is greater than or equal to a value that I designate. If there are no months for that id that are greater than the designated value, then I need to exclude that ID from the data set.

 

An example of what my data looks like:

 

data example;
input id jan2007 feb2007 mar2007;
cards;
1 1000 2000 3000
2 . 500 1000
3 100 100 100
;
run;

  In this example, my desired outcome if I was looking for all values >=1000 would be:

 

id jan2007 feb2007 mar2007 first_month

1  1000      2000       3000        jan2007

2  .             500         1000        mar2007

 

I have been trying to use an array pick out the first time that the value is greater than 1000 for each id. After a while of searching the forums I have gotten so close to figuring it out, but the loop I created picks out the last instance where the do loop is greater than 1000 not the first. The code that I have so far is:

 

data outtest;
set example;
array months {*} jan2018--mar2008;
do i=1 to dim(months);
	if months{i}>1000 then first_month=vname(months{i}) ;
end;
run;

data outtest2;
set outtest;
if first_month='' then delete;
drop i;
run;

This gives me;

 

id jan2008 feb2008 mar2008 first_month

1  1000      2000       3000       mar2008

2  .             500         1000       mar2008

If somebody would be able to help me figure out how to change my code so that it would give me the first instance where the value of the month is >=1000 I would really appreciate it.

 

Thanks so much for the help, 

Tom

 

 


Accepted Solutions
Solution
‎02-16-2018 02:52 PM
Respected Advisor
Posts: 2,981

Re: How to iterate over columns to find the first instance greater than a designated value

[ Edited ]

You have to stop the loop from continuing to execute when you find a value > 1000

 

 

do i=1 to dim(months);
    if months{i}>1000 then do;
        first_month=vname(months{i}) ;
        leave;
    end;
end;

 

--
Paige Miller

View solution in original post


All Replies
Solution
‎02-16-2018 02:52 PM
Respected Advisor
Posts: 2,981

Re: How to iterate over columns to find the first instance greater than a designated value

[ Edited ]

You have to stop the loop from continuing to execute when you find a value > 1000

 

 

do i=1 to dim(months);
    if months{i}>1000 then do;
        first_month=vname(months{i}) ;
        leave;
    end;
end;

 

--
Paige Miller
Contributor
Posts: 20

Re: How to iterate over columns to find the first instance greater than a designated value

Posted in reply to PaigeMiller

That was so easy, I was so close to having it.Thanks for the help!

PROC Star
Posts: 1,765

Re: How to iterate over columns to find the first instance greater than a designated value

data example;
 input id jan2007 feb2007 mar2007;
 cards;
 1 1000 2000 3000
 2 . 500 1000
 3 100 100 100
;
run;

data want;
set example;
array t(*) jan2007--mar2007;
do _n_=1 to dim(t);
if t{_n_}>=1000 then do;
first_month=vname(t{_n_}) ;
output;
return;
end;
end;
run;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 171 views
  • 1 like
  • 3 in conversation