I've got a dataset with 100s of price fields. I want to loop through all price fields and identify the first where the price is less than 0.9 and save the number after "price_" as period. In the row below, the period would be 0.88.
Price_1 | Price_2 | Price_3 | Price_4 | Price_5 | Price_6 | Price_7 | Price_8 | Price_9 | Price_10 | Price_11 |
0.95 | 0.96 | 0.94 | 0.93 | 0.92 | 0.91 | 0.88 | 0.91 | 0.89 | 0.88 | 0.93 |
It sounds like you just want the index into the array. So given this dataset.
data have;
input price_1 - price_11 ;
cards;
0.95 0.96 0.94 0.93 0.92 0.91 0.88 0.91 0.89 0.88 0.93
;
You could create PERIOD using code like.
data want ;
set have ;
array p price_1 - price_11;
do i=1 to dim(p) while (period=.);
if p(i) < .90 then period =i;
end;
run;
You could use another variable to store the actual value if you want.
data want ;
set have ;
array p price_1 - price_11;
do i=1 to dim(p) while (period=.);
if p(i) < .90 then do;
period =i;
first_low_price = p(i);
end;
end;
run;
It sounds like you just want the index into the array. So given this dataset.
data have;
input price_1 - price_11 ;
cards;
0.95 0.96 0.94 0.93 0.92 0.91 0.88 0.91 0.89 0.88 0.93
;
You could create PERIOD using code like.
data want ;
set have ;
array p price_1 - price_11;
do i=1 to dim(p) while (period=.);
if p(i) < .90 then period =i;
end;
run;
You could use another variable to store the actual value if you want.
data want ;
set have ;
array p price_1 - price_11;
do i=1 to dim(p) while (period=.);
if p(i) < .90 then do;
period =i;
first_low_price = p(i);
end;
end;
run;
/* creating data */ data a; price_1=0.95; price_2=0.96; price_3=0.94; price_4=0.93; price_5=0.92; price_6=0.91; price_7=0.88; price_8=0.91 ; price_9=0.89; price_10=0.88; price_11=0.93; run; /* finding price_: variable with first value less than .9 saving value saving variable name and stopping */ data a; set a; length vNameOf_firstPriceLTPoint9 $32 ; array prices {*} price_:; n=dim(prices); do i = 1 to n; if prices{i}<0.9 then do ; first_price_less_than_point9=prices{i}; vNameOf_firstPriceLTPoint9=vname(prices{i}); leave; end; end; run;
This code stops looping after finding, and gives you the name of the variable who's value is less than 0.9.
@brophymj wrote:
I've got a dataset with 100s of price fields. I want to loop through all price fields and identify the first where the price is less than 0.9 and save the number after "price_" as period. In the row below, the period would be 0.88.
Price_1 Price_2 Price_3 Price_4 Price_5 Price_6 Price_7 Price_8 Price_9 Price_10 Price_11 0.95 0.96 0.94 0.93 0.92 0.91 0.88 0.91 0.89 0.88 0.93
From your text wouldn't period be 7? The number after price_ where the value is 0.88 for variable Price_7.
A minor change to @Tom's code:
data want ; set have ; array p price_1 - price_11; do i=1 to dim(p); if p(i) < .90 then do; period =i; leave; end; end; run;
which may work better if you already have a variable named Period and need to refresh the value. The LEAVE instruction says to interrupt the do loop at the current execution.
You already have a good solution, but I wrote this, thus might as well share it:
proc format; invalue price 0.90-high=. ; run; data have (keep=period); infile cards dlm='09'x; informat Price_1-Price_11 price4.; input Price_1-Price_11; period=coalesce(of price_:); cards; 0.95 0.96 0.94 0.93 0.92 0.91 0.88 0.91 0.89 0.88 0.93 ;
Art, CEO, AnalystFinder.com
@art297 super slick, a conventional thinker could have never though. Class act. I wish I had your brains
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.