topic Proc transpose - wide to long (many variables) in SAS Procedures

Proc transpose - wide to long (many variables)

coverup — Sun, 02 Oct 2011 18:08:56 GMT

Hello,

I need to transpose my data to get it into univariate form for proc mixed analyses.

I have multi-level data: One person reported on measures for 5 target people. I have an ID for the person who is completing the questionnaire. Each target person is named person1, person2, person3, etc.

Currently the data is all on one line. The variables are named such that they indicate what person they belong to (e.g. p1similar). There are approximately 8 variables for each target person.

How should my code look so that I can shape my data to have 5 lines of data for the respondent, corresponding to the data for each target person?

e.g.

Id-respondent1 Person1 var var var var var

Id-respondent1 Person2 var var var var var

Id-respondent1 Person 3 var var var var var

Id-respondent2 Person1 var var var var var

Id-respondent2 Person2 var var var var var

Id-respondent2 Person 3 var var var var var

I believe this is the way that my data should look in order to run proc mixed.

I have thus far only been able to find examples with a single variable, and I cannot figure out how to translate it to more than one variable.

I would really appreciate any help from all the smart SAS users out there!

Camilla

Proc transpose - wide to long (many variables)

art297 — Sun, 02 Oct 2011 18:33:26 GMT

You will get the best answer if you provide an example, in the form of a datastep, that shows what your data currently looks like.

Proc transpose - wide to long (many variables)

coverup — Sun, 02 Oct 2011 19:03:09 GMT

I normally read my data in from an excel sheet. Here is a small snippet of what it looks like currently. Since the data is on one long row/line of data, I think it would be too much to paste in here. I have taken an excerpt of 4 people reporting on two people.

I am not sure if this is even visible (it's probably too small).

an example of the variables are:

DiaryID P1name P1Gender P1Age P1Similar P1Judgment P1defensive p1IOS p2name p2gender p2age p2similar p2judgment p2defensive p2IOS.

DiaryID is the respondent ID. P1name/P2name is the number of the target person being reported on (out of 5).

Is that of any help?

Proc transpose - wide to long (many variables)

art297 — Sun, 02 Oct 2011 19:43:46 GMT

There are a number of ways to do it. Since I always have a difficult time remembering the various proc transpose options, and none of my code is EVER production code, I usually address such a problem in the following manner (Note: the type WAS rather small, thus I may have misspelled some of the variable names):

data want (keep=DiaryID name--IOS);

set have;

array names(3) p1name p2name p3name;

array person(3,8) p1gender--p1IOS

p2gender--p2IOS

p3gender--p3IOS;

do i=1 to 3;

name=names(i);

gender=person(i,1);

age=person(i,2);

Ortinter=person(i,3);

Similar=person(i,4);

judgment=person(i,5);

defense=person(i,6);

argument=person(i,7);

IOS=person(i,8);

output;

end;

run;

Proc transpose - wide to long (many variables)

Ksharp — Mon, 03 Oct 2011 08:22:23 GMT

For your case, It is not difficulty as long as you make sure they have the same number of variables.

data temp;
infile datalines truncover;
input DiaryID $ @;
input (Pname  PGender  PAge PSimilar  PJudgment  Pdefensive  pIOS) ($) @;
do while(not missing(Pname));
  output;
  input (Pname  PGender  PAge PSimilar  PJudgment  Pdefensive  pIOS) ($) @;
end;
input;
datalines; 
001 Peter 4 5 7 8 3 8 Arthur 2 3 5 7 8 6 
002 Patrick 4 5 7 8 3 8 Cynthia 2 3 5 7 8 6
;
run;

Ksharp

Re: Proc transpose - wide to long (many variables)

MikeZdeb — Mon, 03 Oct 2011 16:42:13 GMT

Hi ... if your data are in a spreadsheet and you have 5 people reporting within each row,

you can try reading the data directly from the spreadsheet. Using the variable names

that you posted (6 variables within each person) and assuming the data are in SHEET1 ...

libname x 'z:\yourdata.xls';

data new;

set x.'sheet1'n;

length name $10;

array nm(5) p1name p2name p3name p4name p5name;

array va(*) p1gender-numeric-p5ios;

array vb(6) gender age similar judgment defensive ios;

do j = 1 to 5;

name = nm(j);

do k = 1 to 6;

vb(k) = va((j-1)*6 + k);

end;

output;

end;

keep diaryid name gender age similar judgment defensive ios;

run;

libname x clear;