topic Re: How to extract positive and negative numbers for corresponding calculation in SAS Programming

How to extract positive and negative numbers for corresponding calculation

CheerfulChu — Fri, 30 Sep 2016 14:51:38 GMT

Dear Expert

Do you know to extract positive and negative numbers from the columns separately and use it to calculate the standard deviation?

I have a table

Asset rd rd1 rd2 rd3

APPL 0.2 -0.1 -1.2 0.6

IBM 4.1 -2.3 -1.1 4.2

I wish to calculate standard deviation for positve and negative values separately ie for APPL

spread_pos = std(0.2, 0.6)

spread_neg = std(-0.1, -1.2)

spread_ratio = spread_pos / spread_neg

Hence the final table will look like

Asset rd rd1 rd2 rd3 spread_ratio

APPL 0.2 -0.1 -1.2 0.6 0.363

IBM 4.1 -2.3 -1.1 4.2 0.083

Is it possible to do within a data set?

data XXX;

set tableA

if rd > 0 or rd1 > 0 or rd2 > 0 or rd3 >0 then do

spread_pos = std(rd, rd1, rd2, rd3);

end;

if rd < 0 or rd1 < 0 or rd2 < 0 or rd3 <0 then do

spread_neg = std(rd, rd1, rd2, rd3);

end;

spread_ratio = spread_pos / spread_neg;

by asset;

run;

The code above has problem. It will always calculate the standard deviation for all rdX.

Many thanks

Leo

Re: How to extract positive and negative numbers for corresponding calculation

RW9 — Fri, 30 Sep 2016 15:23:31 GMT

You could use three arrays:

array rd...;

array pos...;

array neg...;

do i=1 to dim(rd);

if rd{i} > 0 then pos{i}=rd{i};

else neg{i}=rd{i};

end;

res1=std(of pos{*});

res2=std(of neg{*});

Re: How to extract positive and negative numbers for corresponding calculation

CheerfulChu — Mon, 03 Oct 2016 22:06:38 GMT

Hi Expert,

Code works great.

However, i receive one error which is when there are no elements or only one element when calculating:

 res1=std(of pos{*});

Is it possible to assign res1 = . when the number of elements in pos array is less than 2? I cant use dim(pos) because I have defined:

array pos {12} pos1 - pos12;

Many thanks

Re: How to extract positive and negative numbers for corresponding calculation

RW9 — Tue, 04 Oct 2016 08:10:14 GMT

Use an if statement and dim() function:

if dim(pos) > 2 then res1=std(of pos{*});

No need to fill in . as that is default.