topic HOW: Observation value to new column in SAS Programming

HOW: Observation value to new column

karenktq — Fri, 21 May 2021 15:15:30 GMT

As you can see in Figure 1 below, some of the track ids are repetitive. I would want to move the repetitive row's genre into another column called genre2. Figure 2 is the result I want.

Figure 1

Figure 2

Re: HOW: Observation value to new column

PaigeMiller — Fri, 21 May 2021 15:23:12 GMT

Example with made up data

data have;
input track_id & $12. year genre $16.;
cards;
track_id1  2017 Dance/Electronic
track_id1  2017 Pop
track_id2  2018 Reggae/Ska
track_id3  2015 Country
track_id3  2015 Pop
;
proc transpose data=have out=want prefix=genre;
    by track_id year;
    var genre;
run;

Caution: In SAS, long data sets are usually easier to work with and superior in many ways to wide data sets like the one you say you want. You need to carefully consider what you are doing and be sure that you need a wide data set (because I don't think you do in most cases). If you just want a report, or to put the data into an Excel file, that's usually the only reason to create a wide data set.

Re: HOW: Observation value to new column

karenktq — Fri, 21 May 2021 15:31:40 GMT

Hi, I am actually planning to concatenate the two columns, genre and genre2 (eg; Dance/Electronic/Pop) but I could not do so from the start. I figured separating them into two columns as the first step would be much easier for me to concatenate. Thank you so much!