DATA Step, Macro, Functions and more

Reshaping Data Set

Reply
N/A
Posts: 0

Reshaping Data Set

I have four variables:
Location (x,y,z), q1, q2, and q3 (where each line is a different individual).
q1,q2, and q3 are categorical variables (Disagree, Agree, Strongly Agree).
I would like to create a data set with the following variables:
Location, Question (numeric variable ranging from 1-10), Disagree, Agree, Strongly_Agree
where the data set would look like this:
Location Question Agree Disagree Strongly_Agree
x 1 1 0 0
x 1 0 1 0
x 2 0 0 1
x 2 1 0 0
x 3 1 0 0
x 3 0 1 0
where each line is a different individual (no individual identifier is needed).
If a person agreed with a certain question they get a 1 for Agree and a zero for Disagree and Strong_Agree.
Thank you.
Contributor
Posts: 74

Re: Reshaping Data Set

I assume you mean q1,q2,q3 are 3 questions, with categoric values disagree, agree or stronly.
1.create 3 new variable to represent your category for q1,q2,q3. sicne there are only 3 categories, you may use agree='100',disagree='010',strongly='001'.
2.use proc transpose to get a verticle layout of the original data, for the 3 new variables.
3.parse new values into 3 category vars, ie. agree, disagree, strongly, eg. by using substr.
N/A
Posts: 0

Re: Reshaping Data Set

This looks like it could work.
I'm having some trouble setting up the proc transpose statement, though.
I did this part:
agree='100',disagree='010',strongly='001'
How do I write the proc transpose so that q1, q2, and q3 are all in one column named question (where question=1,2,3)?
Thank you.
Contributor
Posts: 74

Re: Reshaping Data Set

use var statement. check proc transpose documents for correct sytax and requirements. you may need to sort by location if you need to use by in proc transpose. you will get a column _name_ after transposing, with values of 'q1','q2', etc. you may extract the numeric part from that by substr.
seems that you actually have 10 instead of 3 questions? you may want to use array then. format will help translating the categoric values.
Super Contributor
Super Contributor
Posts: 3,174

Re: Reshaping Data Set

PROC TRANSPOSE converts "vertical" to "horizontal", not the other way. To accomplish the objective of taking a file as you have shown, it will take a Data step approach. The SAS support http://support.sas.com/ website has SAS-hosted documentation and supplemental technical and conference reference material. You can use the SEARCH facility at the website or use a Google advanced search argument as shown below:

vertical transpose sas dataset site:sas.com

Scott Barry
SBBWorks, Inc.

234-31: The TRANSPOSE Procedure or How to Turn It Around
Janet Stuelpner, Left Hand Computing, Inc., New Canaan, CT
http://www2.sas.com/proceedings/sugi31/234-31.pdf
Contributor
Posts: 74

Re: Reshaping Data Set

proc transpose does convert vertical to horizontal and vise versa. it worked perfectly for this purpose.

data a;
input Location $ q1 $ q2 $ q3 $;
cards;
x Disagree Agree Stronly
y Agree Disagree Stronly
x Stronly Disagree Disagree
z Stronly Stronly Disagree
x Disagree Stronly Agree
z Stronly Stronly Disagree
x Agree Disagree Stronly
;
run;
data a;
set a;
n=_n_;
run;
proc transpose data=a out=b;
var q1 q2 q3;
by n location;
run;
Respected Advisor
Posts: 3,777

Re: Reshaping Data Set

The key to a horizontal to vertical transpose with PROC TRANSPOSE as you have coded is the necessary evil, unique KEY. In your example. n=_n_;

My data, usually has a unique key so the coding a necessary evil is usually unnecessary. You should consider a data step view in for this task.
Contributor
Posts: 74

Re: Reshaping Data Set

you are absolutely right. the original author said he had a subject ID variable which he didn't need, otherwise that can be used as the unique key. I created the variable n=_n_ to omit the sorting procedure. all I wanted to illustrate in this example is that proc transpose does convert horizontal to vertical. happy SAS coding!
Ask a Question
Discussion stats
  • 7 replies
  • 189 views
  • 0 likes
  • 4 in conversation