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;
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;
@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.
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;
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!
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).
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.