Can somebody help me about retain value?

[bohoo1987]

cusip   prc      m      d        wk      mark

111     -23       6      28       58       1

111      20       6      27       58        .

111      22       6      26       58        .

111      24       6      25       58        .

111      27       6      24       57        .

111     21.5    5      30        57         .

111      25       5      29       57        .

111      24       5      28       57        .

111      27       5      27       57        .

222     -24       5      31       57        1

222      27       5      30       57        .

222      22.5    5      29       56        .

222      29       5      28       56        .

333     -25       4      30       56       1

333      27       4      29       56        .

333      23.5    4      28       56        .

333      26       4      27       55        .

333      29       4      26       55        .

and i want the output like below

cusip   prc      m      d        wk   mark    new_mark

111     -23       6      28       58      1             1

111      20       6      27       58       .             1

111      22       6      26       58       .             1

111      24       6      25       58       .             1

111      27       6      24       57       .              .

111     21.5     5      30       57       .              .

111      25       5      29       57       .              .

111      24       5      28       57       .              .

111      27       5      27       57       .              .

222     -24       5      31       57       1             1

222      27       5      30       57       .              1

222      22.5    5      29       56       .               .

222      29       5      28       56       .               .

333     -25       4      30       56       1             1

333      27       4      29       56       .              1

333      23.5    4      28       56       .              1

333      26       4      27       55       .              .

333      29       4      26       55       .              .

if mark=1 then i want the same cusip and same wk have new_mark value =1

thanks!!

[Steelers_In_DC]

data have;

infile cards dsd;

input cusip    $ prc  m  d  wk mark;

cards;

111,-23,6,28,58,1

111,20,6,27,58,.

111,22,6,26,58,.

111,24,6,25,58,.

111,27,6,24,57,.

111,21.5,5,30,57,.

111,25,5,29,57,.

111,24,5,28,57,.

111,27,5,27,57,.

222,-24,5,31,57,1

222,27,5,30,57,.

222,22.5,5,29,56,.

222,29,5,28,56,.

333,-25,4,30,56,1

333,27,4,29,56,.

333,23,.54,28,56,.

333,26,4,27,55,.

333,29,4,26,55,.

;

run;

data want;

retain new_mark;

set have;

by cusip descending wk;

if first.cusip then new_mark = mark;

if first.wk then new_mark = mark;

run;

[data_null__]

The method would allow you to carry forward more variables more easily.  And makes it easier if you want to keep the variable name.

data cusip;
   input cusip:$3.   prc      m      d        wk      mark;
   cards;
111     -23       6      28       58       1
111      20       6      27       58        .
111      22       6      26       58        .
111      24       6      25       58        .
111      27       6      24       57        .
111     21.5    5      30        57         .
111      25       5      29       57        .
111      24       5      28       57        .
111      27       5      27       57        .
222     -24       5      31       57        1
222      27       5      30       57        .
222      22.5    5      29       56        .
222      29       5      28       56        .
333     -25       4      30       56       1
333      27       4      29       56        .
333      23.5    4      28       56        .
333      26       4      27       55        .
333      29       4      26       55        .
;;;;
   run;
data cusiplocf;
   if 0 then set cusip;
   update cusip(obs=0 keep=cusip wk) cusip(keep=cusip wk mark);
   by cusip descending wk;
   set cusip(drop=cusip wk mark);
   output;
   run;
proc print;
   run;

[bohoo1987]

thank you!! now i can continue my reserch

but have a little confuse about the code logic,can you just say some? Please!

thank you

[data_null__]

You should look at documentation for UPDATE statement to understand exactly what it is doing.  We are hijacking one feature of UPDATE and overriding another.

data cusip;
   input cusip:$3.   prc      m      d        wk      mark;
   cards;
111     -23       6      28       58       1
111      20       6      27       58        .
111      22       6      26       58        .
111      24       6      25       58        .
111      27       6      24       57        .
111     21.5    5      30        57         .
111      25       5      29       57        .
111      24       5      28       57        .
111      27       5      27       57        .
222     -24       5      31       57        1
222      27       5      30       57        .
222      22.5    5      29       56        .
222      29       5      28       56        .
333     -25       4      30       56       1
333      27       4      29       56        .
333      23.5    4      28       56        .
333      26       4      27       55        .
333      29       4      26       55        .
;;;;
   run;

*Parameters to simplify;
%let by=cusip descending wk;
%let locf=mark;

options dkricond=nowarning; *Trick so DESCENDING can be in KEEP or DROP data set option;
data cusiplocf;
   if 0 then set cusip; *Maintain original variable order;

   update cusip(obs=0 keep=&by) cusip(keep=&by &locf); *Use MISSING CHECK feature of UPDATE to carry forward;
   by &by; *Required by UPDATE and used to reset LOCF breaks;

   set cusip(drop=&by &locf); *Get the rest of the variables that are not LOCFed.;

   output; *Change default output for UPDATE which is LAST BY group;
   run;
options dkricond=error;
proc print;
   run;

[bohoo1987]

Thank you! i have try to documentation for UPDATE statement,just not familiar with it!

Anyway,thanks for your help, i will keep practice it

[Steelers_In_DC]

I've been seeing update a lot and I think I'm missing out on it, do you have a favorite document, sug / training material that you could point me in the direction of?

Cheers,

Mark

LetsGoBucs

[AskoLötjönen]

With Proc SQL this is quite easy:

proc sql;

  select *,

           max(mark) as new_mark

  from have

  group by cusip, wk;

quit;

[bohoo1987]

Thank you! I got it!

[Ksharp]

Data Step is also easy.

data have;
infile cards dsd;
input cusip    $ prc  m  d  wk mark;
cards;
111,-23,6,28,58,1
111,20,6,27,58,.
111,22,6,26,58,.
111,24,6,25,58,.
111,27,6,24,57,.
111,21.5,5,30,57,.
111,25,5,29,57,.
111,24,5,28,57,.
111,27,5,27,57,.
222,-24,5,31,57,1
222,27,5,30,57,.
222,22.5,5,29,56,.
222,29,5,28,56,.
333,-25,4,30,56,1
333,27,4,29,56,.
333,23,.54,28,56,.
333,26,4,27,55,.
333,29,4,26,55,.
;
run;
data want;
 set have;
 by  cusip  wk notsorted;
 retain new_mark;
 if first.wk then new_mark=.;
 if not missing(mark) then new_mark=mark;
run;
 

Xia Keshan