topic Proc Tranpose Truncating var values in SAS Programming

Proc Tranpose Truncating var values

michelconn — Mon, 09 Jul 2018 21:20:13 GMT

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;

Re: Proc Tranpose Truncating var values

ballardw — Mon, 09 Jul 2018 21:02:43 GMT

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.

Re: Proc Tranpose Truncating var values

kiranv_ — Mon, 09 Jul 2018 21:04:23 GMT

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;

Re: Proc Tranpose Truncating var values

michelconn — Mon, 09 Jul 2018 21:17:56 GMT

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.

Re: Proc Tranpose Truncating var values

novinosrin — Mon, 09 Jul 2018 21:21:30 GMT

@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

Acinetob	bins	Acinetob
Acinetob	CM1DW20D	2477
Acinetob	CM3DW20D	195
Acinetob	CW1DW20D	163
Acinetob	ODWD	45
Acinetob	W1DW4D	77
Actinoal	bins	Actinoal
Actinoal	CM1DW20D	2431
Actinoal	CM3DW20D	220
Actinoal	CW1DW20D	198
Actinoal	ODWD	69
Actinoal	W1DW4D	6774
Bacillus	bins	Bacillus
Bacillus	CM1DW20D	2456
Bacillus	CM3DW20D	173
Bacillus	CW1DW20D	155
Bacillus	ODWD	78
Bacillus	W1DW4D	785
Bletilla	bins	Bletilla
Bletilla	CM1DW20D	2412
Bletilla	CM3DW20D	135
Bletilla	CW1DW20D	116
Bletilla	ODWD	45
Bletilla	W1DW4D	123

Re: Proc Tranpose Truncating var values

michelconn — Mon, 09 Jul 2018 21:21:49 GMT

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".

Re: Proc Tranpose Truncating var values

michelconn — Mon, 09 Jul 2018 21:22:27 GMT

Well I'm a dummy, thanks.

Re: Proc Tranpose Truncating var values

novinosrin — Mon, 09 Jul 2018 21:23:52 GMT

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