Transpose only one row to one column

[Elena]

Hi,

I have this dataset

data my_data;

input Codice_Comune    anno    tt73    tt74    tt75    tt76    tt77    tt78    tt79    tt80
;
cards;
33001    2011    412    387    350    319    296    273    253    234
33001    2012    415    393    369    333    303    279    255    236
33001    2013    408    386    364    341    307    278    254    232
33001    2014    412    391    367    343    320    288    258    233
33001    2015    414    385    364    341    316    294    263    233
33001    2016    422    394    365    344    320    297    274    243
33001    2017    416    390    362    335    315    292    270    247
33001    2018    0    0    0    0    0    0    0    0
33001    2019    0    0    0    0    0    0    0    0
33001    2020    0    0    0    0    0    0    0    0
33001    2021    0    0    0    0    0    0    0    0
33001    2022    0    0    0    0    0    0    0    0
33002    2011    642    595    548    489    451    406    363    332
33002    2012    650    609    563    519    462    424    378    336
33002    2013    654    605    564    523    480    427    393    348
33002    2014    652    610    563    524    483    443    391    358
33002    2015    646    601    560    514    476    436    396    346
33002    2016    651    599    557    517    472    436    399    360
33002    2017    646    603    551    512    473    427    392    355
33002    2018    0    0    0    0    0    0    0    0
33002    2019    0    0    0    0    0    0    0    0
33002    2020    0    0    0    0    0    0    0    0
33002    2021    0    0    0    0    0    0    0    0
33002    2022    0    0    0    0    0    0    0    0


;

run;

I'd like this dataset where the var new is the transpose from the var tt73--tt79  where anno=2017 .

Thank you!

[error_prone]

What is the question?

[Elena]

Sorry! I corrected my post!

[Tom]

The output spreadsheet you have shown does not not look useful in anyway, but to your question about how to transpose only some rows you could just add a WHERE statement to the PROC TRANPOSE step.

proc transpose data=my_data out=tall prefix=YR ;
  by Codice_Comune ;
  where anno =2017 ;
  id anno;
  var tt73-tt80 ;
run;

This should yield a dataset with two columns: Codice_Comune and YR2017 .

[mkeintz]

1. You want proc transpose  data=mY_date out=want;
2. You only want anno=2017.    Use the WHERE anno=2017 statement
3. You want one row per codice_comune, Use the BY codice_comune statement.
4. You want variables TT73 through TT79.  That's the VARS statement in the PROC TRANSPOSE documentation.

[Ksharp]

data my_data;
input Codice_Comune    anno    tt73    tt74    tt75    tt76    tt77    tt78    tt79    tt80;
cards;
33001    2011    412    387    350    319    296    273    253    234
33001    2012    415    393    369    333    303    279    255    236
33001    2013    408    386    364    341    307    278    254    232
33001    2014    412    391    367    343    320    288    258    233
33001    2015    414    385    364    341    316    294    263    233
33001    2016    422    394    365    344    320    297    274    243
33001    2017    416    390    362    335    315    292    270    247
33001    2018    0    0    0    0    0    0    0    0
33001    2019    0    0    0    0    0    0    0    0
33001    2020    0    0    0    0    0    0    0    0
33001    2021    0    0    0    0    0    0    0    0
33001    2022    0    0    0    0    0    0    0    0
33002    2011    642    595    548    489    451    406    363    332
33002    2012    650    609    563    519    462    424    378    336
33002    2013    654    605    564    523    480    427    393    348
33002    2014    652    610    563    524    483    443    391    358
33002    2015    646    601    560    514    476    436    396    346
33002    2016    651    599    557    517    472    436    399    360
33002    2017    646    603    551    512    473    427    392    355
33002    2018    0    0    0    0    0    0    0    0
33002    2019    0    0    0    0    0    0    0    0
33002    2020    0    0    0    0    0    0    0    0
33002    2021    0    0    0    0    0    0    0    0
33002    2022    0    0    0    0    0    0    0    0
;
run;
proc transpose data=my_data(where=(anno=2017)) out=temp;
by Codice_Comune;
var tt73--tt79;
run;
data temp1;
 set temp;
 by Codice_Comune;
 if first.Codice_Comune then anno=2010;
 anno+1;
 drop _name_;
run;
run;
data want;
 merge my_data temp1;
 by Codice_Comune anno;
run;

[mkeintz · Accepted solution]

If @Elena just wants the single column of NEW for years 2010-2016 then I'd go with the proc transpose as per @Tom'.

But if the NEW column is to be ADDED to the existing dataset, with new values for years 2010-2016, then I drop the proc transpose accompanied by other data steps, and use the power of multiple SET statements inside a single data step:

data want;
  set my_data (where=(anno=2017));

  array tt   {2010:2016} tt73-tt79;
  array _tmp {2010:2016} _temporary_;
  do anno=2010 to 2016; 
    _tmp{anno}=tt{anno};
  end;

  do until (last.codice_comune);
    set my_data;
    by codice_comune;
    if 2010<=anno<=2016 then new=_tmp{anno};
    else new=.;
    output;
  end;
run;

Note: this program assumes every codice_comune has an anno=2017 record, and that the data are sorted by codice_comune (though not neccessarily by anno within codice_comune).

[Elena]

Thank you very much, that's exactly what I want