topic proc transpose in SAS Programming

proc transpose

emaguin — Mon, 27 Jun 2022 20:12:23 GMT

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

Re: proc transpose

Tom — Mon, 27 Jun 2022 20:23:03 GMT

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;

Re: proc transpose

valtina1 — Fri, 28 Oct 2022 20:26:29 GMT

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

Re: proc transpose

Tom — Fri, 28 Oct 2022 20:45:03 GMT

@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;

Re: proc transpose

valtina1 — Thu, 03 Nov 2022 16:13:39 GMT

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.

Re: proc transpose

valtina1 — Thu, 03 Nov 2022 16:18:21 GMT

Sorry, please, ignore my last post.

The last example works perfectly.