Taking missing values into account

[marcushemma]

Hi,

I try to calculate a diffusion index for a number of series. Some begin in January 1990 and some in January 2005. The data is in an excel file.
I'm not sure I have understood the way Rats treats missing values.
I had the impression that Rats would interpret blank cells (or NA's) as missing values but I apparently need to tell Rats something before I run the loops below.
Tried to include the %valid function below but didn't get it right.
My aim is to calculate the diffusion index from 1991:1 and onwards. As it is written now, the index begins in 2006 when there are observations for all series.

Any help would be greatly appreciated

/Marcus


open data diff.xlsx
calendar(m) 1990:1
data(format=xlsx,org=columns)


**********12-month change and new name********
dofor i = c101 to c332
set %s('dl'+%l(i)) = 100*( (i{0})/(i{12}) -1.0)
end dofor i

**************row sums of 1's and 0's for non-negative and negative vaues resp.**
set poscount = 0.0
dofor i = dlc101 to dlc332
set poscount = poscount+(i{0}>=0)
end dofor i

set negcount = 0.0
dofor i = dlc101 to dlc332
set negcount = negcount+(i{0}<0)
end dofor i

**************diffusion index************
set totcount = poscount + negcount
print / totcount poscount negcount

set posshare = 100*(poscount/totcount)
set negshare = 100*(negcount/totcount)
set diff = 100*((poscount/totcount) - (negcount/totcount))
print / totcount poscount negcount diff

[TomDoan]

set poscount = poscount+(i{0}>=0)

will NA out if i{0} is NA. So you need

set poscount = poscount+(%valid(i{0}).and.i{0}>=0)

or

set poscount = poscount+%if(%valid(i{0}),i{0}>=0,0.0)

and similarly for negcount.