Can somebody help me about retain value?

Reply
Occasional Contributor
Posts: 10

Can somebody help me about retain value?

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

Valued Guide
Posts: 858

Re: Can somebody help me about retain value?

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;

Respected Advisor
Posts: 3,788

Re: Can somebody help me about retain value?

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;

5-14-2015 11-27-19 AM.png
Occasional Contributor
Posts: 10

Re: Can somebody help me about retain value?

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

Respected Advisor
Posts: 3,788

Re: Can somebody help me about retain value?

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;
Occasional Contributor
Posts: 10

Re: Can somebody help me about retain value?

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

Valued Guide
Posts: 858

Re: Can somebody help me about retain value?

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

Contributor
Posts: 44

Re: Can somebody help me about retain value?

With Proc SQL this is quite easy:

proc sql;

  select *,

           max(mark) as new_mark

  from have

  group by cusip, wk;

quit;

Occasional Contributor
Posts: 10

Re: Can somebody help me about retain value?

Thank you! I got it!

Super User
Posts: 9,867

Re: Can somebody help me about retain value?

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

Ask a Question
Discussion stats
  • 9 replies
  • 315 views
  • 6 likes
  • 5 in conversation