How can i do
DATA Want;
set Have;
by id;
if first.id then total_vintage =1+depreciation;
total_vintage+depreciation;
run;
Which works fine, but if I want to do the following calculation by id;
total_vintage*(1-depreciation);
???
Base on your example I think you want:
data want; set have; by id; retain total_vintage; if first.id then total_vintage =depreciation; else total_vintage=total_vintage*(1-depreciation); run;
Art, CEO, AnalystFinder.com
YearMonth | ID | depreciation | Want |
201511 | 1 | 1 | 1 |
201512 | 1 | 0,012717037 | 0,987283 |
201601 | 1 | 0,012756447 | 0,974689 |
201602 | 1 | 0,012413319 | 0,96259 |
201603 | 1 | 0,012417115 | 0,950637 |
201604 | 1 | 0,012061455 | 0,939171 |
201605 | 1 | 0,011910294 | 0,927985 |
201606 | 1 | 0,011778051 | 0,917055 |
201607 | 1 | 0,0119076 | 0,906135 |
201608 | 1 | 0,011796668 | 0,895446 |
201609 | 1 | 0,011721272 | 0,88495 |
201610 | 1 | 0,011778189 | 0,874527 |
201611 | 1 | 0,011829325 | 0,864182 |
201612 | 1 | 0,01178267 | 0,854 |
201701 | 1 | 0,011570935 | 0,844118 |
201702 | 1 | 0,011419379 | 0,834479 |
201703 | 1 | 0,011254156 | 0,825087 |
201704 | 1 | 0,011193984 | 0,815851 |
201705 | 1 | 0,011112798 | 0,806785 |
201706 | 1 | 0,011096301 | 0,797833 |
201707 | 1 | 0,011058088 | 0,78901 |
201708 | 1 | 0,01101701 | 0,780318 |
201709 | 1 | 0,011440038 | 0,771391 |
201710 | 1 | 0,011520155 | 0,762504 |
201711 | 1 | 0,01152442 | 0,753717 |
201712 | 1 | 0,01143082 | 0,745101 |
201604 | 2 | 1 | 1 |
201605 | 2 | 0,011477848 | 0,988522 |
201606 | 2 | 0,006636087 | 0,981962 |
201607 | 2 | 0,013963745 | 0,96825 |
201608 | 2 | 0,013915906 | 0,954776 |
201609 | 2 | 0,014880584 | 0,940569 |
201610 | 2 | 0,012836766 | 0,928495 |
201611 | 2 | 0,014111002 | 0,915393 |
201612 | 2 | 0,012796802 | 0,903679 |
201701 | 2 | 0,011764952 | 0,893047 |
201702 | 2 | 0,01255818 | 0,881832 |
201703 | 2 | 0,012798133 | 0,870546 |
201704 | 2 | 0,012469151 | 0,859691 |
201705 | 2 | 0,01328034 | 0,848274 |
201706 | 2 | 0,013028762 | 0,837222 |
201707 | 2 | 0,012920913 | 0,826405 |
201708 | 2 | 0,012739095 | 0,815877 |
201709 | 2 | 0,012776933 | 0,805452 |
201710 | 2 | 0,012114558 | 0,795695 |
201711 | 2 | 0,011621488 | 0,786448 |
201712 | 2 | 0,011033519 | 0,77777 |
Do you mean a retained variable which decreases each row? Post some simple test data in the form of a datastep (so we don't have to type it in), and what the output should be.
data have;
input YearMonth ID depreciation;
datalines;
201511 1 1
201512 1 0.012717037
201601 1 0.012756447
201602 1 0.012413319
201603 1 0.012417115
201604 1 0.012061455
201605 1 0.011910294
201606 1 0.011778051
201607 1 0.0119076
201608 1 0.011796668
201609 1 0.011721272
201610 1 0.011778189
201611 1 0.011829325
201612 1 0.01178267
201701 1 0.011570935
201702 1 0.011419379
201703 1 0.011254156
201704 1 0.011193984
201705 1 0.011112798
201706 1 0.011096301
201707 1 0.011058088
201708 1 0.01101701
201709 1 0.011440038
201710 1 0.011520155
201711 1 0.01152442
201712 1 0.01143082
201604 2 1
201605 2 0.011477848
201606 2 0.006636087
201607 2 0.013963745
201608 2 0.013915906
201609 2 0.014880584
201610 2 0.012836766
201611 2 0.014111002
201612 2 0.012796802
201701 2 0.011764952
201702 2 0.01255818
201703 2 0.012798133
201704 2 0.012469151
201705 2 0.01328034
201706 2 0.013028762
201707 2 0.012920913
201708 2 0.012739095
201709 2 0.012776933
201710 2 0.012114558
201711 2 0.011621488
201712 2 0.011033519
;run;
data want;
input YearMonth ID depreciation vintage;
datalines;
201511 1 1 1
201512 1 0.012717037 0.987282963
201601 1 0.012756447 0.97468874
201602 1 0.012413319 0.962589617
201603 1 0.012417115 0.950637031
201604 1 0.012061455 0.939170965
201605 1 0.011910294 0.927985163
201606 1 0.011778051 0.917055307
201607 1 0.0119076 0.90613538
201608 1 0.011796668 0.895446002
201609 1 0.011721272 0.884950235
201610 1 0.011778189 0.874527124
201611 1 0.011829325 0.864182058
201612 1 0.01178267 0.853999687
201701 1 0.011570935 0.844118112
201702 1 0.011419379 0.834478807
201703 1 0.011254156 0.825087452
201704 1 0.011193984 0.815851436
201705 1 0.011112798 0.806785044
201706 1 0.011096301 0.797832714
201707 1 0.011058088 0.78901021
201708 1 0.011017012 0.780317676
201709 1 0.011440038 0.771390813
201710 1 0.011520155 0.762504271
201711 1 0.01152442 0.753716851
201712 1 0.01143082 0.74510125
201604 2 1 1
201605 2 0.011477848 0.988522152
201606 2 0.006636087 0.981962233
201607 2 0.013963745 0.968250363
201608 2 0.013915906 0.954776282
201609 2 0.014880584 0.940568653
201610 2 0.012836766 0.928494793
201611 2 0.014111002 0.915392801
201612 2 0.012796802 0.903678701
201701 2 0.011764952 0.893046964
201702 2 0.01255818 0.88183192
201703 2 0.012798133 0.870546117
201704 2 0.012469151 0.859691147
201705 2 0.01328034 0.848274156
201706 2 0.013028762 0.837222194
201707 2 0.012920913 0.826404519
201708 2 0.012739095 0.815876873
201709 2 0.012776933 0.805452469
201710 2 0.012114558 0.795694768
201711 2 0.011621488 0.786447611
201712 2 0.011033519 0.777770326
;
run;
DATA want;
set have;
by id;
retain vintage;
if first.id then vintage= 1*(1-Depreciation);
vintage =vintage*(1-Depreciation);
run;
Try this:
data want;
set have;
by id;
retain vintage;
if first.id
then vintage = Depreciation;
else vintage = vintage * (1 - Depreciation);
run;
I believe your logic is incorrect for the calculation:
data want; set have; by id; retain vintage; if first.id then vintage= 1*(1-Depreciation); else vintage=vintage*(1-Depreciation); run;
At row 1, 1-1=0, then 1*0 = 0. Therefore every row's answer will be zero.
Base on your example I think you want:
data want; set have; by id; retain total_vintage; if first.id then total_vintage =depreciation; else total_vintage=total_vintage*(1-depreciation); run;
Art, CEO, AnalystFinder.com
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.