topic Re: Multiple Lines into one line in SAS Programming

Multiple Lines into one line

FK1 — Mon, 23 Jan 2017 15:47:59 GMT

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!

Re: Multiple Lines into one line

PeterClemmensen — Mon, 23 Jan 2017 15:50:28 GMT

Use PROC TRANSPOSE 🙂

reading this should give you the answer:

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

Re: Multiple Lines into one line

ballardw — Mon, 23 Jan 2017 15:59:18 GMT

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.

Re: Multiple Lines into one line

FK1 — Mon, 23 Jan 2017 16:05:19 GMT

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

Re: Multiple Lines into one line

Astounding — Mon, 23 Jan 2017 16:41:50 GMT

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.

Re: Multiple Lines into one line

FK1 — Mon, 23 Jan 2017 16:50:27 GMT

@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?

Re: Multiple Lines into one line

Astounding — Mon, 23 Jan 2017 18:26:14 GMT

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.

Re: Multiple Lines into one line

FK1 — Tue, 24 Jan 2017 16:10:31 GMT

Thank you all very much for your comments.