How to use LAG or Retain function in Array

[123sush]

Hi,

I have a data set with price of 23 drugs in past 5 years.I need to find out each year average price by subtracting current year with previous year divide by two for all 23 drugs and for all years.

I am thinking to use DO loop for year and durgs and use of lag function to get last price.

Can any one one help how to use lag with array. I looked into books they use Retain or lag function with single variable not with multiple.

Thanks,

 

[nickdel]

123sush, you could use proc transpose to reshape your dataset and then from there it will be much easier to do your calculation. i.e.

data Inputs;
input 	@1 DrugName $6. 
		@8 Year $4.
		@13 Price 2.;
datalines;
Drug A 2005 10
Drug B 2005 5
Drug C 2005 4
Drug A 2006 12
Drug B 2006 6
Drug C 2006 3
Drug A 2007 13
Drug B 2007 10
Drug C 2007 3
;
run; 

proc sort data=Inputs;
	by Drugname;
run;

proc transpose data=Inputs out=Output prefix=Price;
	by Drugname;
	id Year;
	var Price;
run;

Hope that helps!

Nick

where would we be without rhetorical questions...

 

[kdt82]

I would actually think getting your data into a normalised (long) form would make you calculations easier. That way you don't have to deal with arrays and subscripts being out of range. Also think you had a typo, the average is the sum of the values divided by the number of values.

data Input1;
input DrugName$1-7 Year Price;
datalines;
Drug A 2005 10
Drug B 2005 5
Drug C 2005 4
Drug A 2006 12
Drug B 2006 6
Drug C 2006 3
Drug A 2007 13
Drug B 2007 10
Drug C 2007 3
;
run; 

proc sort;
by drugname;

data final1(drop=_:);
    set input1;
    by drugname;
    _lagp = lag1(price);
    if first.drugname then avgprice = price;
    else avgprice = mean(_lagp, price);
run;

 

[123sush]

Hi Nick and KDT , Thanks for the reply. Actually my dataset is some what like this

year drug1 drug2 drug3 drug4 drug5 drug6 drug7 drug8 drug9
1992 10 15 12 13 12 15 12 16 21
1993 12 12 12 11 12 20 11 18 19
1994 .......
1995 ...........................
1996 .................

I need to do calculations for each drug with year
(price of drug1 in 1993 - price of drug1 in 1992)
----------------------------------------------- X 100
price of drug1 in 1992

with the output I have to do more calulations and generate the report.

I am writing the code like this:

/* read the data set*/
data drug_price;
infile 'C:\SAS\drug_price.txt';
input year drugname1-drugname9;
run;

proc sort data=drug_price output=sort_drug_price;
by year;
run;

 

[kdt82]

Ok, so you want to calculate percentage increase/decrease of price rather than the average. This should do it. The ifn function here is just syntactic sugar for and "if/else" statement. When dividing a value, it's a good idea to think how you want to handle missing information so you don't get a division by zero error, here I am just ignoring calculations on such values, but you might want to do something different with it.

data have;
input year  drug1 drug2 drug3 drug4 drug5 drug6 drug7 drug8 drug9;
cards;
1992   10     15   12    13    12    15    12    16     0
1993   12     12   12    11    12    20    11     18    19
;
run;

proc transpose data=have out=trans(rename=(_name_=drugname col1=price));
by year;
run;

proc sort data=trans;
by drugname year;
run;

data want(drop=_:);
    set trans;
    by drugname;
    _lagp = lag1(price);
    if _lagp then * Catch division by zero errors;
        pct_change = ifn(first.drugname,0,(price-_lagp)/_lagp*100);
run;

proc print;run;

 

[123sush]

Thanks all of you for the help. I have submitted the report with in my ded line.

Thanks again!