PROC TRANSPOSE vs CAS Action TRANSPOSE

[TimStettner]

Hey,

I'm actually struggling a bit with the behaviour of the "good old" PROC TRANSPOSE and the CAS Action TRANSPOSE.

I want to transpose the four Petal/Sepal Columns by the column species

Species	PetalWidth	PetalLength	SepalWidth	SepalLength
Setosa	10	20	100	200
Versicolor	5	6	7	8
Virginica	1	2	3	4

Using

proc transpose data=iris2 out=iris4;
by Species;
quit;

would lead to:

Species	_NAME_	COL1
Setosa	SepalLength	200
Setosa	SepalWidth	100
Setosa	PetalWidth	10
Setosa	PetalLength	20
Versicolor	...	...
Virginica	...	...

So far, so good.

When I now switch to the new world in CAS and using CASL actions, the TRANSPOSE action behaves in another way.

Here you need to name an "ID" column, and in the output you will get a transposed column for each unique value of this one (and not the expected "COL1" column).

proc cas;
	transpose.transpose / 
		table={caslib="casuser", name="IRIS2", groupBy="Species"},
		id="Species",
		name="Variable",
		casOut={name="IRIS4", replace=TRUE, caslib="casuser"}
		;
quit;

Species	Variable	Setosa	Versicolor	Virginica
Versicolor	SepalLength		8
Versicolor	SepalWidth		7
...	...
Virginica	SepalLength			4

Ist this something you can change with settings? I do not find anything except the description of exactly this behaviour.

And you cannot skip the ID column (as in PROC TRANSPOSE) as this is a required parameter.

To merge the three columns in an additional step afterwords is possible, but there should be a smarter solution, right?

Any ideas? 

Cheers, Tim

[DerylHollick · Accepted solution]

You can create an id column with the names you want. 

proc cas;
	transpose.transpose / 
		table={caslib="casuser", name="IRIS2", groupBy="Species", 
               computedVars={name="colname"} computedVarsProgram="colname='COL1';"},
		id="colname",
		name="Variable",
		casOut={name="IRIS4", replace=TRUE, caslib="casuser"}
		;
quit;

[DavidHD]

Hey,

you can make use of the dataShaping.wideToLong CAS Action. Please note that this action was first introduced in SAS Viya 2021.1.2

proc cas;
	dataShaping.wideToLong /
		table={caslib='casuser', name='iris'},
		id='Species',
		casOut={caslib='casuser', name='iris4', replace=true}
	;
quit;

Best, David

[TimStettner]

Great, thank you for your quick replies.

Both ways are working, but as we are actually still in Viya 3.5 I have to wait for the "wide to long" action until Viya 4.