Multiple Lines into one line

[FK1]

Hello Folks,

I have a sas table "HAVE", which has an ID variable in it  and several numeric as well as character variables. Now, I want for each value of ID exactly ONE obsservation in my output dataset "WANT". Here is an example. This is my "Have" dataset:

And this is what I want as output:

Does anybody have an idea how I get this output?

Thanks in advance Folks!

[PeterClemmensen]

Use PROC TRANSPOSE 🙂

reading this should give you the answer:

http://www.ats.ucla.edu/stat/sas/modules/ltow_transpose.htm

[ballardw]

What will you do with the resulting data? Or is the result supposed to be a report of some kind?

What you are doing is often referred as going from long to wide format. The long format is often easier for many operations as you know how many variables to process for each record. The wide format often has issues with varying numbers of Variable1 values that can get a bit challenging to do anything with.

[FK1]

Hi ballardw,

you are exactly pointing out the issue of using wide format. However in the aboce mentioned case I do not want to further use the data in order to calcualte anything. But I want to merge the "Want" dataset with the "ID" as a unique key onto another dataset. Therefore I do not want to have repeating by values in the "ID" variable.....

[Astounding]

For each new column you create, you will need to think up a variable name.  SAS doesn't let you keep data within a data set unless there is a variable name attached to it.  If you spell out what the variable names should be, the rest is relatively easy.

[FK1]

@Astounding: How can I think up variable names if the number of variables needs to be detemined dynamically (depending on the frequency) of a particular value of the ID variable?

[Astounding]

You have to have names for each variable.  There is no way around it.  However, you don't necessarily need to know all the names ahead of time.

One usual way would use PROC TRANSPOSE to follow this plan ... 

Take a variable named TEXT, and from it create TEXT1, TEXT2, TEXT3, etc. (as many as needed).  TEXT3 might be blank for some IDs if that ID only started with 2 observations.  This plan is compatible with the way PROC TRANSPOSE would handle the problem.  You don't know how many variables would be created, and you don't have to know.  You only have to agree that the original variable name, with a numbered suffix added, would fit your needs here.

[FK1]

Thank you all very much for your comments.