How to get non-negative value at some situation

Reply
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 !!

111.jpg

Esteemed Advisor
Esteemed Advisor
Posts: 7,203

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.

Esteemed Advisor
Esteemed Advisor
Posts: 7,203

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.

Grand Advisor
Posts: 9,576

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

Grand Advisor
Posts: 9,576

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

Respected Advisor
Posts: 3,124

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;

Ask a Question
Discussion stats
  • 11 replies
  • 408 views
  • 0 likes
  • 5 in conversation