DATA Step, Macro, Functions and more

Multiple Lines into one line

Reply
Occasional Contributor FK1
Occasional Contributor
Posts: 15

Multiple Lines into one line

[ Edited ]

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:

 

Have.JPG

 

And this is what I want as output:

 

want.JPG

 

Does anybody have an idea how I get this output?

Thanks in advance Folks!

PROC Star
Posts: 554

Re: Multiple Lines into one line

Use PROC TRANSPOSE Smiley Happy

 

reading this should give you the answer:

 

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

 

 

Super User
Posts: 10,550

Re: Multiple Lines into one line

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.

Occasional Contributor FK1
Occasional Contributor
Posts: 15

Re: Multiple Lines into one line

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.....
Super User
Posts: 5,099

Re: Multiple Lines into one line

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.

 

Occasional Contributor FK1
Occasional Contributor
Posts: 15

Re: Multiple Lines into one line

@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?
Super User
Posts: 5,099

Re: Multiple Lines into one line

[ Edited ]

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.

 

 

Occasional Contributor FK1
Occasional Contributor
Posts: 15

Re: Multiple Lines into one line

Thank you all very much for your comments.

Ask a Question
Discussion stats
  • 7 replies
  • 189 views
  • 0 likes
  • 4 in conversation