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

 

 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
1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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;

 

View solution in original post

5 REPLIES 5
Tom
Super User Tom
Super User

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;

 

JerryV
SAS Employee
/* 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.

 

ballardw
Super User

@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.

 

art297
Opal | Level 21

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

 

novinosrin
Tourmaline | Level 20

@art297 super slick, a conventional thinker could have never though. Class act. I wish I had your brains

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
  • 5 replies
  • 966 views
  • 3 likes
  • 6 in conversation