topic Re: Can somebody help me about retain value? in SAS Programming

Can somebody help me about retain value?

bohoo1987 — Thu, 14 May 2015 15:49:59 GMT

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

Re: Can somebody help me about retain value?

Steelers_In_DC — Thu, 14 May 2015 16:10:58 GMT

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;

Re: Can somebody help me about retain value?

data_null__ — Thu, 14 May 2015 16:29:28 GMT

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;

Re: Can somebody help me about retain value?

bohoo1987 — Fri, 15 May 2015 06:42:55 GMT

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

Re: Can somebody help me about retain value?

data_null__ — Fri, 15 May 2015 10:17:02 GMT

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;

Re: Can somebody help me about retain value?

AskoLötjönen — Fri, 15 May 2015 11:43:01 GMT

With Proc SQL this is quite easy:

proc sql;

select *,

max(mark) as new_mark

from have

group by cusip, wk;

quit;

Re: Can somebody help me about retain value?

bohoo1987 — Fri, 15 May 2015 13:26:09 GMT

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

Re: Can somebody help me about retain value?

bohoo1987 — Fri, 15 May 2015 13:28:12 GMT

Thank you! I got it!

Re: Can somebody help me about retain value?

Ksharp — Fri, 15 May 2015 14:03:26 GMT

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

Re: Can somebody help me about retain value?

Steelers_In_DC — Fri, 15 May 2015 16:38:56 GMT

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