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

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

Tommy1 — Fri, 16 Feb 2018 19:47:16 GMT

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

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

PaigeMiller — Fri, 16 Feb 2018 19:51:15 GMT

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;

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

Tommy1 — Fri, 16 Feb 2018 19:53:18 GMT

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

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

novinosrin — Fri, 16 Feb 2018 19:55:35 GMT

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;