Proc transpose of character variable name and label

[andypandy_swe]

I've got a dataset containing three variables (ID, QUESTION, ANSWER) and I want to transpose QUESTION (long to wide).

QUESTION is a character variable with a character format.

The desired result is a dataset with the variables ID, FIRST_QUESTION, ANOTHER_QUESTION, LAST_QUESTION (this are the unformatted values of QUESTION.

However I would like the formatted values of QUESTION to be the labels of the transposed variables.

If I just do a proc transpose I get the formatted values as the names of the transposed variables. So it's:

ID, This is the first question, Now let's answer another, Finally the last one.

I can get the unformatted values by removing the format before transposing but then I can't get the formatted values as labels...

Any help would be greatly appreciated.   

[ed_sas_member · Accepted solution]

Hi @andypandy_swe 

I suggest that you run a preliminary data step so as to create a second variable equivalent to QUESTION, but with unformatted values.

Best,

proc format;
	value $ Fquestion "a" = "aaa"
					  "b" = "bbb"
					  "c" = "ccc";
run;

data have;
	input ID QUESTION $ ANSWER $;
	format QUESTION $Fquestion.;
	datalines;
1 a xxx
1 b yyy
1 c zzz
2 a aaa
2 b bbb
2 c ccc
;
run;

data have2;
	set have;
	format QUESTION $Fquestion. question_unf;
	question_unf = QUESTION;
run;
	
proc transpose data=have2 out=want (drop=_:);
	var question_unf;
	id question;
	by id;
run;

[andypandy_swe]

Thanks! I just figured it out myself two minutes ago 🙂

Although the proc transpose need to be as below for it to give the desired results...

proc transpose data=have2 out=want (drop=_:);
var answer;
id question_unf;
idlabel question;
by id;
run;