topic Re: Restructure messy data in New SAS User

Restructure messy data

kk89 — Mon, 05 Nov 2018 19:59:27 GMT

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!

Re: Restructure messy data

ballardw — Mon, 05 Nov 2018 20:27:06 GMT

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

Re: Restructure messy data

kk89 — Mon, 05 Nov 2018 20:34:38 GMT

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.

Re: Restructure messy data

Reeza — Mon, 05 Nov 2018 21:19:59 GMT

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.

Re: Restructure messy data

kk89 — Mon, 05 Nov 2018 21:59:28 GMT

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.

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!

Re: Restructure messy data

novinosrin — Mon, 05 Nov 2018 22:21:12 GMT

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;

Re: Restructure messy data

novinosrin — Mon, 05 Nov 2018 22:24:48 GMT

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

Re: Restructure messy data

kk89 — Tue, 06 Nov 2018 02:40:31 GMT

Thank you novinosrin. Your solution works!

Re: Restructure messy data

novinosrin — Tue, 06 Nov 2018 20:36:18 GMT

Ok Thanks