Restructure messy data

[kk89]

I have the first 2 rows of data which looks as given (Q1-Q9 are the variable names)

Q1	Q2	Q3	Q4	Q5	Q6	Q7	Q8	Q9
Colour			Shape		Children
red	blue	green	square	circle	1	2	3	4

I would like the rows to look as given below

Q1	Q2	Q3	Q4	Q5	Q6	Q7	Q8	Q9
Colour - red	Colour - blue	Colour - green	Shape - square	Shape - circle	Children - 1	Children - 2	Children - 3	Children - 4
red	blue	green	square	circle	1	2	3	4

I have used SAS for sometime now but not for these kinds of manipulation and I would appreciate any help.Thank you!

[ballardw]

---

@kk89 wrote:

I have the first 2 rows of data which looks as given (Q1-Q9 are the variable names)

Q1	Q2	Q3	Q4	Q5	Q6	Q7	Q8	Q9
Colour			Shape		Children
red	blue	green	square	circle	1	2	3	4

I would like the rows to look as given below

Q1	Q2	Q3	Q4	Q5	Q6	Q7	Q8	Q9
Colour - red	Colour - blue	Colour - green	Shape - square	Shape - circle	Children - 1	Children - 2	Children - 3	Children - 4
red	blue	green	square	circle	1	2	3	4

I have used SAS for sometime now but not for these kinds of manipulation and I would appreciate any help.Thank you!

---

Is this only the first two lines of data of a file or does this need to be done in the middle of the data set?

And why would one value need the word colour combined with  the actual color (sorry I'm American) and others just the actual color?

This almost seems like you want to have a Label of 'Colour red' associated with the variable Q1 to describe the column. Is this the case?

[kk89]

This is just the first two rows of the data.

Yes, I would want a Label of 'Colour red' associated with the variable Q1 to describe the column. I'm sorry if I didn't describe this well.

[Reeza]

How is the file coming originally, as text? If so, can you please attach a file with a few lines that illustrate exactly how you're receiving the data.

[kk89]

The file that I received was in csv format. I have provided an example of how the data looks like in the attached file.

id	What colours do you like?			What shapes do you like?		How many children do you have?
	red	blue	green	square	circle	1	2	3	4

As the csv file shows, there seems to be two headers which I would like to combine to 1 as given below. 

id

What colours do you like? - red

What colours do you like? - blue

What colours do you like? - green

What shapes do you like? - square

What shapes do you like? - circle

How many children do you have? - 1

How many children do you have? - 2

How many children do you have? - 3

How many children do you have? - 4

Please do let me know if you have any additional questions. Thanks a lot!

[novinosrin · Accepted solution]

data have;
input (Q1	Q2	Q3	Q4 	Q5	 Q6 	Q7	Q8	Q9) (:$20.);
cards;
Colour 	 . .	 	Shape 	. 	 Children	. . . 	 
red 	blue 	green	square	circle 	1	2	3	4
;

data want;
set have;
array t(*) q:;
if _n_=1 then do;
set have(firstobs=2 obs=2 rename=(q1-q9=j1-j9));
array k(*)j:;
do i=1 to dim(t);
if missing(t(I)) then t(I)=t(i-1);
end;
do i=1 to dim(t);
t(I)=catx('-',t(i),k(i));
end;
end;
drop i j:;
run;

[novinosrin]

Hmm I missed to see your latest post and answered your original post. 

[kk89]

Thank you novinosrin. Your solution works!

[novinosrin]

Ok Thanks