Help using Base SAS procedures

Proc transpose - wide to long (many variables)

Reply
Occasional Contributor
Posts: 8

Proc transpose - wide to long (many variables)

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 Star
Posts: 7,467

Proc transpose - wide to long (many variables)

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.

Occasional Contributor
Posts: 8

Proc transpose - wide to long (many variables)

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.

sas help proc transpose 2.PNG

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 Star
Posts: 7,467

Proc transpose - wide to long (many variables)

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;

Super User
Posts: 10,018

Proc transpose - wide to long (many variables)

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

Valued Guide
Posts: 765

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

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;

Ask a Question
Discussion stats
  • 5 replies
  • 1779 views
  • 0 likes
  • 4 in conversation