- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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!
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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).
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set
Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets
--------------------------
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Sorry! I corrected my post!
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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 .
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- You want proc transpose data=mY_date out=want;
- You only want anno=2017. Use the WHERE anno=2017 statement
- You want one row per codice_comune, Use the BY codice_comune statement.
- You want variables TT73 through TT79. That's the VARS statement in the PROC TRANSPOSE documentation.
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set
Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets
--------------------------
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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).
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set
Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets
--------------------------
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thank you very much, that's exactly what I want