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

 

 

Cattura.JPG

Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
mkeintz
PROC Star

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

--------------------------

View solution in original post

7 REPLIES 7
error_prone
Barite | Level 11
What is the question?
Elena
Obsidian | Level 7

Sorry! I corrected my post!

Tom
Super User Tom
Super User

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
PROC Star
  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.
--------------------------
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

--------------------------
Ksharp
Super User

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
PROC Star

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

--------------------------
Elena
Obsidian | Level 7

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

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
  • 7645 views
  • 1 like
  • 5 in conversation