topic Re: Proc transpose resulted in COL1 with some unexpected results in SAS Procedures

Proc transpose resulted in COL1 with some unexpected results

sahoositaram555 — Wed, 25 Mar 2020 10:06:20 GMT

data a;

input subjid visitc sodium potassium bicarbonate glucose bloodureanitrogen creatinine SGPT SGOT GGT;

cards;

01001 3th_visit 12.2 34.2 33.2 33 22 123 90 89 89.3

01001 6th_visit 22.3 41.6 56.1 89 12 133 80 49 39.3

01001 9th_visit 20.2 40.2 23.2 23 12 230 30 99 19.3

01002 3th_visit 13.2 41.2 12.2 13 43 634 65 14 23.2

01002 6th_visit 90.2 21.2 33.2 33 22 123 90 89 89.3

01002 9th_visit 29.2 34.2 30.2 13 12 126 30 29 99.3

;

run;

when i transpose this by below code:

proc transpose data=a out=t_a name=variables ;
by subjid visit;
var sodium--ggt;
run;

i have an output similar to below where COL1 is getting a length of 5000 and the values which i can't use for any of the derivation , neither i can use any function to extract the value. example if i use a cat function or compress function to reduce the length or even i tried by assigning a lesser length by mentioning it before set statement so that PDV get the shorter length but it's not working. i have tried to apped the value to nother variable, but its putting missing value there as well.

subjid visit variables _Label_ COL1

01001 3th_visit sodium 3rdweekvisit 12.2

ANyone who has ever faced this issue kindly do shoot a reply.

Re: Proc transpose resulted in COL1 with some unexpected results

Kurt_Bremser — Wed, 25 Mar 2020 10:23:07 GMT

I cannot recreate your issue. When I run this:

data a;
input subjid visitc sodium potassium bicarbonate glucose bloodureanitrogen creatinine SGPT SGOT GGT;
cards;
01001 3th_visit 12.2 34.2 33.2 33 22 123 90 89 89.3
01001 6th_visit 22.3 41.6 56.1 89 12 133 80 49 39.3
01001 9th_visit 20.2 40.2 23.2 23 12 230 30 99 19.3
01002 3th_visit 13.2 41.2 12.2 13 43 634 65 14 23.2
01002 6th_visit 90.2 21.2 33.2 33 22 123 90 89 89.3
01002 9th_visit 29.2 34.2 30.2 13 12 126 30 29 99.3
;

proc transpose data=a out=t_a name=variables ;
by subjid visitc;
var sodium--ggt;
run;

proc contents data=t_a;
run;

I get this result:

Alphabetische Liste der Variablen und Attribute
#	Variable	Typ	Länge	Etikett
4	COL1	Num	8	 
5	COL2	Num	8	 
6	COL3	Num	8	 
1	subjid	Num	8	 
3	variables	Char	17	NAME DER FRÜHEREN VARIABLE
2	visitc	Num	8

Re: Proc transpose resulted in COL1 with some unexpected results

sahoositaram555 — Wed, 25 Mar 2020 10:31:51 GMT

Thanks @Kurt_Bremser.

Re: Proc transpose resulted in COL1 with some unexpected results

FreelanceReinh — Wed, 25 Mar 2020 10:46:08 GMT

Hi @sahoositaram555,

@sahoositaram555 wrote:

... COL1 is getting a length of 5000 ...

This indicates that the variable list sodium--ggt contains a character variable (of length 5000). In this case all transposed numeric values will be converted to character values of that maximum length (which will be mentioned in the log: "NOTE: Numeric variables in the input data set will be converted to character in the output data set.").

First, check if sodium, potassium etc. are numeric variables. If so (and you want COL1 to be numeric as well), restrict the variable list to numeric variables:

var sodium-numeric-ggt;

Please note that this will exclude all character variables. If need be, you may want to transpose these in a separate PROC TRANSPOSE step.

Re: Proc transpose resulted in COL1 with some unexpected results

Kurt_Bremser — Wed, 25 Mar 2020 11:39:18 GMT

So you first need to follow Maxim 3 and get to Know Your Data. Once you have determined the culprit, you need to go searching why a column that you expected to be numeric ended up being character of length 5000.

This is usually the result of an unsound import process (read: using proc import, or - gasp - Excel files). The proper way to be about it is to fix that import process. Replace Excel files with text-based files, and use a proper data step to read the data into SAS.

Re: Proc transpose resulted in COL1 with some unexpected results

ballardw — Wed, 25 Mar 2020 14:35:19 GMT

@sahoositaram555 wrote:

i have an output similar to below where COL1 is getting a length of 5000

That means that SOMETHING in your data is text. The maximum length a numeric variable will have in SAS is 8.

So apparently your example data step does not reflect the actual data you may be attempting to transpose.

Perhaps it is time to run proc contents on the actual data set you are attempting to transpose and show us the result descriptions of the variables.