## How to get non-negative value at some situation

Occasional Contributor
Posts: 10

# How to get non-negative value at some situation

Hey! you guys experts can anyone help me solve my problem?! I have stock here for weeks

grouped by cusip and wk if lastwprc<0 then i want to get the non-negative prc for the same cusip same week but the positive prc.

like this.....

cusip   prc   wk   lastwprc

111     -23    51      -23

111     -23    51       .

111      22    51       .

i want to let lastwprc change to 22 if lastwprc<0  and positive prc is not sure at which position and there are different cusip

thanks you all !!

Super User
Posts: 9,599

## Re: How to get non-negative value at some situation

Hi,

Not tested but something like:

proc sql;

create table WANT as

select  A.*,

case  when A.LASTWPRC >= 0 then B.MAX_PRC

else B.MIN_PRC end as RESULT

from    WORK.HAVE A

left join (select distinct CUSIP,

min(PRC) as MIN_PRC,

max(PRC) as MAX_PRC

from   WORK.HAVE

group by CUSIP) B

on      A.CUSIP=B.CUSIP;

quit;

Occasional Contributor
Posts: 10

## Re: How to get non-negative value at some situation

thank you for trying solve my problem,but in your code,if lastwprc<0 then lastwprc=min(prc) is the cusip's minimum prc at same cusip,

but what i want to do is : if lastwprc < 0 then lastwprc will equall to that week's first non-negative prc.

Super User
Posts: 9,599

## Re: How to get non-negative value at some situation

Then just update the grouping:

left join (select distinct CUSIP,

WK,

min(PRC) as MIN_PRC,

max(PRC) as MAX_PRC

from   WORK.HAVE

group by CUSIP,

WK) B

on      A.CUSIP=B.CUSIP

and     A.WK=B.WK;

Occasional Contributor
Posts: 10

## Re: How to get non-negative value at some situation

i have try this but not enough condition that i didn't say clearly enough.

Anyway, thank you for trying help me.And there are clear condition below,hope you can keep help me to solve it.

Super User
Posts: 10,784

## Re: How to get non-negative value at some situation

Post some more data (not picture) and the output you want .

Occasional Contributor
Posts: 10

## Re: How to get non-negative value at some situation

sorry!maybe i didn't say it clearly enough.

there's part of data sample

cusip   prc      m      d        wk    lastwprc

111     -23       6      28       58       -23

111     -23       6      27       58        .

111      22       6      26       58        .

111      24       6      25       58        .

111      27       6      24       58        .

111     -21.5    5      30       57       -21.5

111     -22       5      29       57        .

111      24       5      28       57        .

111      27       5      27       57        .

222      24       5      31       57        24

222      27       5      30       57        .

222      22.5    5      29       57        .

222      29       5      28       57        .

333     -25       4      30       56       -25

333     -27       4      29       56        .

333     -23.5    4      28       56        .

333      26       4      27       56        .

333      29       4      26       56        .

m=month, d=day,wk=week, lastwprc= last week prc, wk= intindex('week.4', date, 2800);

i descending data by cusip wk to get every month's( the last week every month) last prc for lastwprc(i will lag it after i make sure every lastwprc are positive), and then i want to let the negative lastwprc to be that month's and same week first non-negative prc if the lastwprc is negative,

like below:

cusip   prc      m      d        wk    lastwprc

111     -23       6      28       58       22

111     -23       6      27       58        .

111      22       6      26       58        .

111      24       6      25       58        .

111      27       6      24       58        .

111     -21.5    5      30       57       24

111     -22       5      29       57        .

111      24       5      28       57        .

111      27       5      27       57        .

222      24       5      31       57       24

222      27       5      30       57        .

222      22.5    5      29       57        .

222      29       5      28       57        .

333     -25       4      30       56       26

333     -27       4      29       56        .

333     -23.5    4      28       56        .

333      26       4      27       56        .

333      29       4      26       56        .

that month same week's first non-negative prc not the max prc

Super User
Posts: 10,784

## Re: How to get non-negative value at some situation

OK. If I understand what you mean .

```data have;
input cusip \$   prc m      d        wk lastwprc;
cards;
111     -23 6      28       58 -23
111     -23 6      27       58 .
111      22 6      26       58 .
111      24 6      25       58 .
111      27 6      24       58 .
111     -21.5 5      30       57 -21.5
111     -22 5      29       57 .
111      24 5      28       57 .
111      27 5      27 57        .
222      24 5      31       57 24
222      27 5      30       57 .
222      22.5 5      29       57 .
222      29 5      28       57 .
333     -25 4      30       56 -25
333     -27 4      29       56 .
333     -23.5 4      28       56 .
333      26 4      27       56 .
333      29 4      26       56 .
;
run;

data want;
merge have have(keep=cusip wk prc rename=(prc=_prc) where=(_prc gt 0));
by cusip descending wk;
if . < lastwprc < 0 then lastwprc=_prc;
drop _prc;
run;
```

Xia Keshan

Occasional Contributor
Posts: 10

## Re: How to get non-negative value at some situation

sorry!maybe i didn't say it clearly enough.

there's part of data sample

cusip   prc      m      d        wk    lastwprc

111     -23       6      28       58       -23

111     -23       6      27       58        .

111      22       6      26       58        .

111      24       6      25       58        .

111      27       6      24       58        .

111     -21.5    5      30       57       -21.5

111     -22       5      29       57        .

111      24       5      28       57        .

111      27       5      27       57        .

222      24       5      31       57        24

222      27       5      30       57        .

222      22.5    5      29       57        .

222      29       5      28       57        .

333     -25       4      30       56       -25

333     -27       4      29       56        .

333     -23.5    4      28       56        .

333      26       4      27       56        .

333      29       4      26       56        .

m=month, d=day,wk=week, lastwprc= last week prc, wk= intindex('week.4', date, 2800);

i descending data by cusip wk to get every month's( the last week every month) last prc for lastwprc(i will lag it after i make sure every lastwprc are positive), and then i want to let the negative lastwprc to be that month's and same week first non-negative prc if the lastwprc is negative,

like below:

cusip   prc      m      d        wk    lastwprc

111     -23       6      28       58       22

111     -23       6      27       58        .

111      22       6      26       58        .

111      24       6      25       58        .

111      27       6      24       58        .

111     -21.5    5      30       57       24

111     -22       5      29       57        .

111      24       5      28       57        .

111      27       5      27       57        .

222      24       5      31       57       24

222      27       5      30       57        .

222      22.5    5      29       57        .

222      29       5      28       57        .

333     -25       4      30       56       26

333     -27       4      29       56        .

333     -23.5    4      28       56        .

333      26       4      27       56        .

333      29       4      26       56        .

that month same week's first non-negative prc not the max prc

Posts: 3,167

## Re: How to get non-negative value at some situation

Here is one way (let us know if you run into performance issue):

data have;

input cusip\$   prc m      d        wk lastwprc;

cards;

111     -23 6      28       58 -23

111     -23 6      27       58 .

111      22 6      26       58 .

111      24 6      25       58 .

111      27 6      24       58 .

111     -21.5 5      30       57 -21.5

111     -22 5      29       57 .

111      24 5      28       57 .

111      27 5      27 57        .

222      24 5      31       57 24

222      27 5      30       57 .

222      22.5 5      29       57 .

222      29 5      28       57 .

333     -25 4      30       56 -25

333     -27 4      29       56 .

333     -23.5 4      28       56 .

333      26 4      27       56 .

333      29 4      26       56 .

;

proc sql;

create table want as

select *,

case

when .<lastwprc <0 then (select prc from have where cusip=a.cusip and m=a.m and prc>0 having d=max(d))

else lastwprc

end

as new_lastwprc

from have a;

quit;

Haikuo

Occasional Contributor
Posts: 10

## Re: How to get non-negative value at some situation

That's awesome!! It's work! I am grateful for you guys help! Thank you all!

Yes, I'm doing momentum performance issue,I'm trying to do 52-week high strategy use weekly data not monthly data.

so I need the last week's non-negative price to calculate every week's 52-high ratio and compare with weekly momentum and J&T momentum.

Contributor
Posts: 52

## Re: How to get non-negative value at some situation

A solution is the following:

data have;
input cusip prc m d wk lastwprc;
cards;
111     -23       6      28       58       -23
111     -23       6      27       58        .
111      22       6      26       58        .
111      24       6      25       58        .
111      27       6      24       58        .
111     -21.5     5      30       57       -21.5
111     -22       5      29       57        .
111      24       5      28       57        .
111      27       5      27       57        .
222      24       5      31       57        24
222      27       5      30       57        .
222      22.5     5      29       57        .
222      29       5      28       57        .
333     -25       4      30       56       -25
333     -27       4      29       56        .
333     -23.5     4      28       56        .
333      26       4      27       56        .
333      29       4      26       56        .
;
run;

data want(drop=k1 k2);
length cusip prc m d wk lastwprc 8.;
k1=0; k2=0;

do until (last.wk);
set have(keep=cusip wk prc);
by cusip wk notsorted;
if (k1=0) and    (prc<0) then k1=1;
if (k1=1) and not(prc<0) then do; k1=2; k2=prc; end;
end;

do until (last.wk);
set have;
by cusip wk notsorted;
if (lastwprc<0)and not(lastwprc=.) and (k1=2) then lastwprc=k2;
output;
end;

run;

Discussion stats
• 11 replies
• 472 views
• 0 likes
• 5 in conversation