BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Elena
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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;

View solution in original post

7 REPLIES 7
Kurt_Bremser
Super User

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?

Elena
Obsidian | Level 7
Codice_comune   Anno    tt75    tt80    tt80s
33001200214177 
33001200314888 
330012004136104 
330012005137106 
330012006134115 
330012007118122 
330012008110121 
330012009117112 
330012010111109 
330012011120110 
330012012133105102
3300120131329490
3300120141349796
3300120151319488
33001201612210599
330012017115107118
330012018  113
330012019  111
330012020  111
330012021  107
330012022  93
330022002227113 
330022003232122 
330022004233138 
330022005233159 
330022006219172 
330022007203184 
330022008210178 
330022009203181 
330022010193173 
330022011217168 
330022012227163165
330022013216171161
330022014205175158
330022015214157143
330022016197175166
330022017196180182
330022018  176
330022019  177
330022020  174
330022021  159
330022022  155

 

That's ok!

Elena
Obsidian | Level 7

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)

 

 

 

 

 

 

Kurt_Bremser
Super User

@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).

 

Ksharp
Super User

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;
Elena
Obsidian | Level 7

Thank you,

I'll work on it.

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 848 views
  • 0 likes
  • 3 in conversation