I need to transpose (wide to long) a dataset. 49 variables: Id, resp1-resp16, err1-err16, item1-item16. resp is string(character) err and item are numeric. So it seems like
proc transpose data=lec1 out=leclong; by studyid;
var resp1-resp16 lecerr1-lecerr16 item1-item16; run;
ought to work fine although i have never done this in sas (spss many times both ways). First of all is sas capable of doing this? I've read the documentation and also Cody and i notice that they don't show a multivariable example, which seems strange because proc mixed may well require transposing multiple variables. Or, is my syntax wrong?
The expected output dataset would have 4 columns with 16 rows per studyid value. I get a result but it's botched up. unusable.
Thanks, Gene Maguin
PROC TRANSPOSE will not handle that directly, especially if you have mixed numeric and character variables.
But it is trivial to do it yourself.
data leclong;
set lec1;
array _r resp1-resp16;
array _l lecerr1-lecerr16;
array _i item1-item16;
do index=1 to 16;
resp = _r[index];
lecerr = _l[index];
item = _i[index];
output;
end;
drop resp1-resp16 lecerr1-lecerr16 item1-item16;
run;
PROC TRANSPOSE will not handle that directly, especially if you have mixed numeric and character variables.
But it is trivial to do it yourself.
data leclong;
set lec1;
array _r resp1-resp16;
array _l lecerr1-lecerr16;
array _i item1-item16;
do index=1 to 16;
resp = _r[index];
lecerr = _l[index];
item = _i[index];
output;
end;
drop resp1-resp16 lecerr1-lecerr16 item1-item16;
run;
Tom,
Very helpful script, very glad I found it.
However, it works for me only for character variables.
When I have only numerical variables (not mixed with character once) and try to transform them, I have an error:
ERROR: Too many variables defined for the dimension(s) specified for the array d.
array d {40} drugdose:;
do index=1 to 40;
drugdoses = d[index];
output;
end;
What did I miss?
Thank you,
Val
@valtina1 wrote:
Tom,
Very helpful script, very glad I found it.
However, it works for me only for character variables.
When I have only numerical variables (not mixed with character once) and try to transform them, I have an error:
ERROR: Too many variables defined for the dimension(s) specified for the array d.
array d {40} drugdose:;
do index=1 to 40;
drugdoses = d[index];
output;
end;What did I miss?
Thank you,
Val
Why did you tell the data step compiler there were 40 variables whose names start with DRUGDOSE when there aren't 40 of them?
Why did you tell it ANY number at all? The data step compiler will count how many variables are in the list you provided it.
array d drugdose:;
do index=1 to dim(d);
drugdoses = d[index];
output;
end;
Hi,
I tried everything... with missing data and w/o missing, with categorical IDs and w/o IDs - it doesn't work.
data have;
input drugdose1 drugdose2 drugdose3;
datalines;
30 30 0.12
0.2 0.2 0.2
0.2 0.2 0.2
;
run;
data want;
set have;
array d drugdose:;
do index=1 to dim(d);
drugdoses = d[index];
output;
end;
run;
... don't know what I have missed...
For categorical variables it is perfect.
Sorry, please, ignore my last post.
The last example works perfectly.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.