DATA Step, Macro, Functions and more

Proc Tranpose Truncating var values

Accepted Solution Solved
Reply
Contributor
Posts: 60
Accepted Solution

Proc Tranpose Truncating var values

[ Edited ]

I am attempting to use proc tranpose but in the output data set the values in the _NAME_ variable keep getting truncated. Below is the transpose code I am using. Any suggestions to prevent SAS from truncating the data? I tried using array but can't figure out how to replicated the _NAME_ variable proc tranpose provides. The only thing I haven't tried yet is using a macro loop in a similar fashion as an array.

 

test data set

 

data Abun_dw20d_summary ;    
informat bins $30.; format bins $30.; length bins $30.; input bins $ CM1DW20D CM3DW20D CW1DW20D ODWD W1DW4D; datalines; Acinetobacter 2477 195 163 45 77 Actinoalloteichus 2431 220 198 69 6774 Bacillus 2456 173 155 78 785 Bletilla 2412 135 116 45 123 ; run; proc transpose data=Abun_dw20d_summary out=bint; var _all_; by bins; run;

Accepted Solutions
Solution
Monday
PROC Star
Posts: 1,770

Re: Proc Tranpose Truncating var values

Posted in reply to michelconn

@michelconn  That's not truncating although it looks like it does. Just take the mouse and expand the view table window or run a proc print

 

data Abun_dw20d_summary ;                    
   input bins $ CM1DW20D CM3DW20D CW1DW20D ODWD W1DW4D;
   datalines;         
Acinetobacter 2477 195  163 45 77
Actinoalloteichus 2431 220  198 69 6774
Bacillus 2456 173  155 78 785
Bletilla 2412 135  116 45 123
;   
run;


proc transpose data=Abun_dw20d_summary out=bint;
    var _all_;
    by bins;
run;

proc print;run;

SAS Output

The SAS System

Obs bins _NAME_ COL1123456789101112131415161718192021222324
AcinetobbinsAcinetob
AcinetobCM1DW20D2477
AcinetobCM3DW20D195
AcinetobCW1DW20D163
AcinetobODWD45
AcinetobW1DW4D77
ActinoalbinsActinoal
ActinoalCM1DW20D2431
ActinoalCM3DW20D220
ActinoalCW1DW20D198
ActinoalODWD69
ActinoalW1DW4D6774
BacillusbinsBacillus
BacillusCM1DW20D2456
BacillusCM3DW20D173
BacillusCW1DW20D155
BacillusODWD78
BacillusW1DW4D785
BletillabinsBletilla
BletillaCM1DW20D2412
BletillaCM3DW20D135
BletillaCW1DW20D116
BletillaODWD45
BletillaW1DW4D123

View solution in original post


All Replies
Super User
Posts: 13,507

Re: Proc Tranpose Truncating var values

Posted in reply to michelconn

At least in your example data the BINS variable truncated to 8 characters because it isn't read with more than 8 characters.

So _name_ would only be 8.

Contributor
Posts: 60

Re: Proc Tranpose Truncating var values

I added additional code to prevent the bins variable from being truncated. Although this doesn't solve the issue of the values in the _NAME_ variable of the transposed data being truncated. For example the value "CM1DW20D" is being read as "CM1DW20D".
PROC Star
Posts: 509

Re: Proc Tranpose Truncating var values

Posted in reply to michelconn

Truncation is happening while reading data itself and you need to fix while reading the data by using informat or length statement as shown below

data Abun_dw20d_summary ;  
informat bins $30.;
   input bins $ CM1DW20D CM3DW20D CW1DW20D ODWD W1DW4D;
   datalines;         
Acinetobacter 2477 195  163 45 77
Actinoalloteichus 2431 220  198 69 6774
Bacillus 2456 173  155 78 785
Bletilla 2412 135  116 45 123
;   
run;
Contributor
Posts: 60

Re: Proc Tranpose Truncating var values

This doesn't appear to fix the issue. I'm not worried about the bins variable that was just an example. After I transpose the dataset the values in the _NAME_ variable are being truncated. For example the value "CM1DW20D" apepears as "CM1DW20" in the _NAME_ variable on the transposed dataset.
Solution
Monday
PROC Star
Posts: 1,770

Re: Proc Tranpose Truncating var values

Posted in reply to michelconn

@michelconn  That's not truncating although it looks like it does. Just take the mouse and expand the view table window or run a proc print

 

data Abun_dw20d_summary ;                    
   input bins $ CM1DW20D CM3DW20D CW1DW20D ODWD W1DW4D;
   datalines;         
Acinetobacter 2477 195  163 45 77
Actinoalloteichus 2431 220  198 69 6774
Bacillus 2456 173  155 78 785
Bletilla 2412 135  116 45 123
;   
run;


proc transpose data=Abun_dw20d_summary out=bint;
    var _all_;
    by bins;
run;

proc print;run;

SAS Output

The SAS System

Obs bins _NAME_ COL1123456789101112131415161718192021222324
AcinetobbinsAcinetob
AcinetobCM1DW20D2477
AcinetobCM3DW20D195
AcinetobCW1DW20D163
AcinetobODWD45
AcinetobW1DW4D77
ActinoalbinsActinoal
ActinoalCM1DW20D2431
ActinoalCM3DW20D220
ActinoalCW1DW20D198
ActinoalODWD69
ActinoalW1DW4D6774
BacillusbinsBacillus
BacillusCM1DW20D2456
BacillusCM3DW20D173
BacillusCW1DW20D155
BacillusODWD78
BacillusW1DW4D785
BletillabinsBletilla
BletillaCM1DW20D2412
BletillaCM3DW20D135
BletillaCW1DW20D116
BletillaODWD45
BletillaW1DW4D123
Contributor
Posts: 60

Re: Proc Tranpose Truncating var values

Posted in reply to novinosrin
Well I'm a dummy, thanks.
PROC Star
Posts: 1,770

Re: Proc Tranpose Truncating var values

Posted in reply to michelconn

lol That makes two of us. I am surpised the cop @ballardw who corrects me and makes me learn didn't point that . lol 

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 86 views
  • 0 likes
  • 4 in conversation