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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.