Hi,
I have this dataset
DATA TEMP;
INPUT @1 codice_comune 5.
@7 anno 4.
@12 tt75 3.
@15 tt80 3.
;
DATALINES;
33001 2002 141 77
33001 2003 148 88
33001 2004 136 104
33001 2005 137 106
33001 2006 134 115
33001 2007 118 122
33001 2008 110 121
33001 2009 117 112
33001 2010 111 109
33001 2011 120 110
33001 2012 133 105
33001 2013 132 94
33001 2014 134 97
33001 2015 131 94
33001 2016 122 105
33001 2017 115 107
33002 2002 227 113
33002 2003 232 122
33002 2004 233 138
33002 2005 233 159
33002 2006 219 172
33002 2007 203 184
33002 2008 210 178
33002 2009 203 181
33002 2010 193 173
33002 2011 217 168
33002 2012 227 163
33002 2013 216 171
33002 2014 205 175
33002 2015 214 157
33002 2016 197 175
33002 2017 196 180
run;
I must create a new var for every codice_comune from anno 2012 to anno 2022 with this formula
Tt80s=tt75(anno2007)*tt80(anno2007)/tt75(anno2002)
The final dataset must be:
Codice_comune Anno tt75 tt80 tt80s
33001 2002 141 77
33001 2003 148 88
33001 2004 136 104
33001 2005 137 106
33001 2006 134 115
33001 2007 118 122
33001 2008 110 121
33001 2009 117 112
33001 2010 111 109
33001 2011 120 110
33001 2012 133 105 102
33001 2013 132 94 90
33001 2014 134 97 96
33001 2015 131 94 88
33001 2016 122 105 99
33001 2017 115 107 118
33001 2018 113
33001 2019 111
33001 2020 111
33001 2021 107
33001 2022 93
33002 2002 227 113 113
33002 2003 232 122 111
33002 2004 233 138 111
33002 2005 233 159 107
33002 2006 219 172 93
33002 2007 203 184 194
33002 2008 210 178 211
33002 2009 203 181 245
33002 2010 193 173 304
33002 2011 217 168 328
33002 2012 227 163 165
33002 2013 216 171 161
33002 2014 205 175 158
33002 2015 214 157 143
33002 2016 197 175 166
33002 2017 196 180 182
33002 2018 176
33002 2019 177
33002 2020 174
33002 2021 159
33002 2022 155
thank
Here could give a start .
DATA TEMP;
INPUT codice_comune
anno
tt75
tt80
;
DATALINES;
33001 2002 141 77
33001 2003 148 88
33001 2004 136 104
33001 2005 137 106
33001 2006 134 115
33001 2007 118 122
33001 2008 110 121
33001 2009 117 112
33001 2010 111 109
33001 2011 120 110
33001 2012 133 105
33001 2013 132 94
33001 2014 134 97
33001 2015 131 94
33001 2016 122 105
33001 2017 115 107
;
run;
proc sql;
select *,(select tt75 from temp where codice_comune=a.codice_comune and anno=a.anno-5)*
(select tt80 from temp where codice_comune=a.codice_comune and anno=a.anno-5)/
(select tt75 from temp where codice_comune=a.codice_comune and anno=a.anno-10) as want
from temp as a;
quit;
Please review your formula. I can't fit it reasonably to your "want" data. And why does your tt80s start in 2012 for 33001, but in 2002 for 33002?
Codice_comune | Anno | tt75 | tt80 | tt80s |
33001 | 2002 | 141 | 77 | |
33001 | 2003 | 148 | 88 | |
33001 | 2004 | 136 | 104 | |
33001 | 2005 | 137 | 106 | |
33001 | 2006 | 134 | 115 | |
33001 | 2007 | 118 | 122 | |
33001 | 2008 | 110 | 121 | |
33001 | 2009 | 117 | 112 | |
33001 | 2010 | 111 | 109 | |
33001 | 2011 | 120 | 110 | |
33001 | 2012 | 133 | 105 | 102 |
33001 | 2013 | 132 | 94 | 90 |
33001 | 2014 | 134 | 97 | 96 |
33001 | 2015 | 131 | 94 | 88 |
33001 | 2016 | 122 | 105 | 99 |
33001 | 2017 | 115 | 107 | 118 |
33001 | 2018 | 113 | ||
33001 | 2019 | 111 | ||
33001 | 2020 | 111 | ||
33001 | 2021 | 107 | ||
33001 | 2022 | 93 | ||
33002 | 2002 | 227 | 113 | |
33002 | 2003 | 232 | 122 | |
33002 | 2004 | 233 | 138 | |
33002 | 2005 | 233 | 159 | |
33002 | 2006 | 219 | 172 | |
33002 | 2007 | 203 | 184 | |
33002 | 2008 | 210 | 178 | |
33002 | 2009 | 203 | 181 | |
33002 | 2010 | 193 | 173 | |
33002 | 2011 | 217 | 168 | |
33002 | 2012 | 227 | 163 | 165 |
33002 | 2013 | 216 | 171 | 161 |
33002 | 2014 | 205 | 175 | 158 |
33002 | 2015 | 214 | 157 | 143 |
33002 | 2016 | 197 | 175 | 166 |
33002 | 2017 | 196 | 180 | 182 |
33002 | 2018 | 176 | ||
33002 | 2019 | 177 | ||
33002 | 2020 | 174 | ||
33002 | 2021 | 159 | ||
33002 | 2022 | 155 |
That's ok!
Ok, then which values did you use for year 2013 / 33001 that gave you the value 90?
I used
tt75 anno 2008= 110
tt80 anno 2008=121
tt75 anno 2002=148
110*121/148=90
so the formula is
tt80s(anno t) = tt75 (anno t-5) * tt80(anno t-5) / tt75 (anno t-10)
(anno is year)
@Elena wrote:
I used
tt75 anno 2008= 110
tt80 anno 2008=121
tt75 anno 2002=148
110*121/148=90
so the formula is
tt80s(anno t) = tt75 (anno t-5) * tt80(anno t-5) / tt75 (anno t-10)
(anno is year)
Got it. Was constantly looking at the wrong line and confused by the result.
@Ksharp's suggestion is probably the best solution (simple code).
Here could give a start .
DATA TEMP;
INPUT codice_comune
anno
tt75
tt80
;
DATALINES;
33001 2002 141 77
33001 2003 148 88
33001 2004 136 104
33001 2005 137 106
33001 2006 134 115
33001 2007 118 122
33001 2008 110 121
33001 2009 117 112
33001 2010 111 109
33001 2011 120 110
33001 2012 133 105
33001 2013 132 94
33001 2014 134 97
33001 2015 131 94
33001 2016 122 105
33001 2017 115 107
;
run;
proc sql;
select *,(select tt75 from temp where codice_comune=a.codice_comune and anno=a.anno-5)*
(select tt80 from temp where codice_comune=a.codice_comune and anno=a.anno-5)/
(select tt75 from temp where codice_comune=a.codice_comune and anno=a.anno-10) as want
from temp as a;
quit;
Thank you,
I'll work on it.
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.