topic Re: Transposing with many columns in SAS Programming

Transposing with many columns

plf515 — Thu, 10 Jan 2019 18:06:03 GMT

I have a data set (imported from a .csv file) that has data on 80 children who had to try to identify a bunch of words. Each child has 6 variables associated with him/her at the individual level: Participant, age, gender, bilingual, esl and linguistic text. Then, for each of a bunch of words, they can get a "c", "i" or missing. So it looks something like this:

Participant Age Gender Bilingual ESL Linguistic I I_ve the I_m of now .....

1 79 m y y y c c c c c i

2 84 f y y y c i c i i

Now, I would like to run analyses on the likelihood of a word being correctly guessed, so I need the data like this:

Word Participant Age Gender Bilingual ESL Linguistic Correct

I 1 79 m y y y y

I 2 84 f y y y y

.......

I_ve 1 79 m y y y y

I_ve 2 84 f y y y y

...

How can I do this?

Re: Transposing with many columns

ballardw — Thu, 10 Jan 2019 18:26:26 GMT

@plf515 wrote:

I have a data set (imported from a .csv file) that has data on 80 children who had to try to identify a bunch of words. Each child has 6 variables associated with him/her at the individual level: Participant, age, gender, bilingual, esl and linguistic text. Then, for each of a bunch of words, they can get a "c", "i" or missing. So it looks something like this:

Participant    Age Gender Bilingual   ESL Linguistic    I I_ve the I_m   of now .....

1                     79                   m            y           y        y    c      c     c     c    c    i

2                     84                   f              y            y        y           c     i             c    i   i

.

.

.

.

Now, I would like to run analyses on the likelihood of a word being correctly guessed, so I need the data like this:

Word     Participant Age   Gender    Bilingual    ESL   Linguistic    Correct

I                   1            79        m             y               y         y                 y

I                    2           84        f               y               y         y                 y

.......

I_ve    1 79        m             y               y         y                 y

I_ve 2           84        f               y               y         y                 y

...

How can I do this?

First thing, it is best to post data in the form of a data step.

data have;
 informat Participant $4. Gender Bilingual ESL Linguistic I I_ve the I_m of now $1. ;
 input Participant  Age Gender Bilingual ESL Linguistic I I_ve the I_m of now ;
datalines;
1  79  m y y y c c c c c i
2  84  f y y y c i . c i i
;
run;

proc transpose data =have out=want name=word;
   by participant age gender bilingual esl linguistic;
   var I  I_ve  the  I_m   of  now ;/* guessing these are "word" variables*/
run;

This assumes that the data is sorted by participant and other variables on the BY statement.

The output variable COL1 will have the values of the scores(?).

If you want c to display as y and I as n (note that the forum won't allow us to show a single lower case I ) then apply a custom format to the variable when used and a label for Col1 (or rename the variable as desired)

I tend to really dislike character variables for yes/no type values and prefer 1/0 numeric values as statistics are easier to calculate for certain types of output and some model procedures require dependent values to be numeric.

Re: Transposing with many columns

PaigeMiller — Thu, 10 Jan 2019 18:32:26 GMT

Use the %TRANSPOSE macro

http://support.sas.com/resources/papers/proceedings13/538-2013.pdf

Re: Transposing with many columns

data_null__ — Thu, 10 Jan 2019 19:28:17 GMT

data words;
   infile cards firstobs=2;
   input Participant Age (Gender Bilingual   ESL  Linguistic    I  I_ve  the  I_m   of  now)(:$1.); 
   cards;
Participant    Age           Gender  Bilingual   ESL  Linguistic    I  I_ve  the  I_m   of  now 
1               79           m            y       y        y        c     c    c     c    c   i
2               84           f            y       y        y        c     i    .     c    i   i
;;;;
   run;
proc print;
   run;   

proc transpose data=words out=want(rename=(col1=Response)) name=word;
   by Participant Age Gender Bilingual ESL Linguistic;
   var i--now;
   run;
proc print;
   run;