topic Transpose and rename column with variable value in New SAS User

Transpose and rename column with variable value

FRAFLUTE — Mon, 22 Jul 2019 13:57:53 GMT

Good morning!

I find always good solution here! This is very hard for me... for this i need your help!

I've this table

App / Matrix / Fee

123456 12/10000 452,00
123456 15/10000 555,00
123456 12/15000 585,00
123456 15/15000 604,00
123457 12/10000 452,00
123457 15/10000 555,00
123457 12/15000 585,00
123457 15/15000 604,00
123458 12/10000 645,00
123458 15/10000 518,00
123458 12/15000 588,00
123458 15/15000 604,00

I want to transpose the table and want the result like this:

App Fee 12/10000 15/10000 12/15000 15/15000

123456 fee   452,00     555,00     585,00     604,00
123457 fee   452,00      555,00     585,00     604,00
123458 fee   645,00      518,00     588,00     604,00

Is it possible?

I start from this code:

data matrix;
input app $ matrix $ fee $ ;
datalines;
123456 12/10000 452,00 
123456 15/10000 555,00 
123456 12/15000 585,00 
123456 15/15000 604,00 
123457 12/10000 452,00 
123457 15/10000 555,00 
123457 12/15000 585,00 
123457 15/15000 604,00 
123458 12/10000 645,00 
123458 15/10000 518,00 
123458 12/15000 588,00 
123458 15/15000 604,00 
;
PROC SORT
	DATA=WORK.MATRIX(KEEP=matrix fee app)
	OUT=WORK.SORTTempTableSorted
	;
	BY app;
RUN;
PROC TRANSPOSE DATA=WORK.SORTTempTableSorted
	OUT=WORK.TRNSTRANSPOSED_0000(LABEL="WORK.MATRIX trasposto")
	PREFIX=Colonna
	NAME=Origine
	LABEL=Etichetta
;
	BY app;
	VAR matrix fee;

Re: Transpose and rename column with variable value

PaigeMiller — Mon, 22 Jul 2019 14:05:47 GMT

@FRAFLUTE wrote:

I want to transpose the table and want the result like this:

App       Fee 12/10000 15/10000 12/15000 15/15000

123456 fee   452,00     555,00     585,00     604,00
123457 fee   452,00      555,00     585,00     604,00
123458 fee   645,00      518,00     588,00     604,00

Is it possible?

SAS variable names cannot contain the slash character. If you can accept something else (an underscore) in place of the slash character, then yes it is possible.

Re: Transpose and rename column with variable value

Reeza — Mon, 22 Jul 2019 16:02:12 GMT

PROC TRANSPOSE DATA=WORK.SORTTempTableSorted
	OUT=WORK.TRNSTRANSPOSED_0000(LABEL="WORK.MATRIX trasposto")
	PREFIX=Colonna;
	BY app;
	VAR matrix fee;
ID Matrix;
IDLABEL matrix;
run;

Try adding in an ID and IDLABEL columns.

@FRAFLUTE wrote:

Good morning!

I find always good solution here! This is very hard for me... for this i need your help!

I've this table

App      / Matrix    / Fee

123456 12/10000 452,00
123456 15/10000 555,00
123456 12/15000 585,00
123456 15/15000 604,00
123457 12/10000 452,00
123457 15/10000 555,00
123457 12/15000 585,00
123457 15/15000 604,00
123458 12/10000 645,00
123458 15/10000 518,00
123458 12/15000 588,00
123458 15/15000 604,00

I want to transpose the table and want the result like this:

App       Fee 12/10000 15/10000 12/15000 15/15000

123456 fee   452,00     555,00     585,00     604,00
123457 fee   452,00      555,00     585,00     604,00
123458 fee   645,00      518,00     588,00     604,00

Is it possible?

I start from this code:
data matrix;
input app $ matrix $ fee $ ;
datalines;
123456 12/10000 452,00 
123456 15/10000 555,00 
123456 12/15000 585,00 
123456 15/15000 604,00 
123457 12/10000 452,00 
123457 15/10000 555,00 
123457 12/15000 585,00 
123457 15/15000 604,00 
123458 12/10000 645,00 
123458 15/10000 518,00 
123458 12/15000 588,00 
123458 15/15000 604,00 
;
PROC SORT
	DATA=WORK.MATRIX(KEEP=matrix fee app)
	OUT=WORK.SORTTempTableSorted
	;
	BY app;
RUN;
PROC TRANSPOSE DATA=WORK.SORTTempTableSorted
	OUT=WORK.TRNSTRANSPOSED_0000(LABEL="WORK.MATRIX trasposto")
	PREFIX=Colonna
	NAME=Origine
	LABEL=Etichetta
;
	BY app;
	VAR matrix fee;

Re: Transpose and rename column with variable value

FRAFLUTE — Wed, 24 Jul 2019 13:48:24 GMT

This is the correct solution but the problem now is that the column is in character format.

i tried to make this now:

((input(COMPRESS('12 / 15000'n),COMMAX10.2))) FORMAT=COMMAX10.2,

it run but now the coulmn is renamed '_TEMA001'...

If use the attribute "AS" he rename the column...

How can I convert the character format after the transpose in a numeric format?

Thank's!

Re: Transpose and rename column with variable value

Tom — Wed, 24 Jul 2019 14:16:27 GMT

It is probably easier to set that in the original file and transpose the numeric and character variables separately.

data tall ;
  input id $ name :$32. char_value :$200. num_value 8;
cards;
1 first_name Fred .
1 last_name Flintstone .
1 age . 57 
2 first_name Barney
2 last_name Rubble
2 age . 50
;
proc transpose data=tall out=chars ;
  by id;
  where not missing(char_value);
  id name ;
  var char_value;
run;
proc transpose data=tall out=nums;
  by id;
  where not missing(num_value);
  id name ;
  var num_value;
run;
data want ;
  merge nums chars;
  by id;
run;

You might need to work harder to make sure you don't end up making both a character and numeric variable with the same name.

Note that you will not be able to use Art's concatenation trick if there are multiple observations for the same numeric variable. You could concatenate the values into a character variable. Or you could change the name of the variable to make each name unique with a given ID group (for example by adding a numeric suffix).