topic Re: Reshaping Data Set in SAS Programming

Reshaping Data Set

deleted_user — Tue, 03 Nov 2009 19:07:13 GMT

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.

Re: Reshaping Data Set

abdullala — Tue, 03 Nov 2009 19:42:05 GMT

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.

Re: Reshaping Data Set

deleted_user — Tue, 03 Nov 2009 20:12:21 GMT

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.

Re: Reshaping Data Set

abdullala — Tue, 03 Nov 2009 20:22:31 GMT

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.

Re: Reshaping Data Set

sbb — Tue, 03 Nov 2009 22:08:45 GMT

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

Re: Reshaping Data Set

abdullala — Wed, 04 Nov 2009 14:46:46 GMT

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;

Re: Reshaping Data Set

data_null__ — Wed, 04 Nov 2009 16:16:09 GMT

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.

Re: Reshaping Data Set

abdullala — Wed, 04 Nov 2009 16:27:43 GMT

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!