- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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 |
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
/* 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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@art297 super slick, a conventional thinker could have never though. Class act. I wish I had your brains