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
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;
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;
That was so easy, I was so close to having it.Thanks for the help!
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.