BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Tommy1
Quartz | Level 8

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

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

3 REPLIES 3
PaigeMiller
Diamond | Level 26

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
Tommy1
Quartz | Level 8

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

novinosrin
Tourmaline | Level 20
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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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