input subjid visitc sodium potassium bicarbonate glucose bloodureanitrogen creatinine SGPT SGOT GGT;
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
when i transpose this by below code:
proc transpose data=a out=t_a name=variables ;
by subjid visit;
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.
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
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.
... 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:
Please note that this will exclude all character variables. If need be, you may want to transpose these in a separate PROC TRANSPOSE step.
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.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.