BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
andypandy_swe
Obsidian | Level 7

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.   

 

1 ACCEPTED SOLUTION

Accepted Solutions
ed_sas_member
Meteorite | Level 14

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;

View solution in original post

2 REPLIES 2
ed_sas_member
Meteorite | Level 14

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
Obsidian | Level 7

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;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 542 views
  • 2 likes
  • 2 in conversation