Hello SAS users:
A message from jmp lover cheating with base SAS:
JMP has an option in Tables that is called "Split by" where you can split a column, mapping several rows on one column to one row in several columns.
Any examples on how to do the same thing in SAS?
I was able to get the example popcorn.jmp data and here is the SAS version of "Split by" as @Reeza mentioning.
data popcorn;
infile datalines dlm='09'x missover;
input popcorn:$8. oil_amt :$8. batch:$8. yield trial;
datalines;
gourmet little large 8.2 2
gourmet little large 8.6 1
gourmet little small 12.1 1
gourmet little small 15.9 2
gourmet lots large 9.2 1
gourmet lots large 9.8 2
gourmet lots small 16 2
gourmet lots small 18 1
plain little large 8.2 1
plain little large 8.8 2
plain little small 9.9 1
plain little small 10.1 2
plain lots large 8.8 2
plain lots large 10.4 1
plain lots small 7.4 2
plain lots small 10.6 1
;
run;
PROC SORT DATA=POPCORN;
BY popcorn oil_amt batch yield ;
RUN;
PROC TRANSPOSE DATA=POPCORN OUT=TRANSPOSE(drop=_name_ ) ;
BY popcorn oil_amt batch;
iD trial;
var yield;
RUN;
What does "split a column" mean? 🙂
Wow that was fast response - thank you!
This is what JMP means by split by
https://www.jmp.com/support/help/14/split-columns.shtml
and this is the different feature they have on transpose
https://www.jmp.com/support/help/14/transpose-rows-and-columns.shtml
I think both concepts are related but there are subtle differences
It would be better if you can provide a sample data you have and how JMP "split by", so that someone here can give you the same result in SAS using your sample data as source.
Here is an example from JMP webpage:
https://www.jmp.com/support/help/14/examples-of-splitting-columns.shtml
Here is JSL
Data Table( "Popcorn" ) << Split(
Split By( :trial ),
Split( :yield ),
Group( :popcorn ),
Sort by Column Property
)
I was able to get the example popcorn.jmp data and here is the SAS version of "Split by" as @Reeza mentioning.
data popcorn;
infile datalines dlm='09'x missover;
input popcorn:$8. oil_amt :$8. batch:$8. yield trial;
datalines;
gourmet little large 8.2 2
gourmet little large 8.6 1
gourmet little small 12.1 1
gourmet little small 15.9 2
gourmet lots large 9.2 1
gourmet lots large 9.8 2
gourmet lots small 16 2
gourmet lots small 18 1
plain little large 8.2 1
plain little large 8.8 2
plain little small 9.9 1
plain little small 10.1 2
plain lots large 8.8 2
plain lots large 10.4 1
plain lots small 7.4 2
plain lots small 10.6 1
;
run;
PROC SORT DATA=POPCORN;
BY popcorn oil_amt batch yield ;
RUN;
PROC TRANSPOSE DATA=POPCORN OUT=TRANSPOSE(drop=_name_ ) ;
BY popcorn oil_amt batch;
iD trial;
var yield;
RUN;
I think that’s PROC TRANSPOSE or the TRANSPOSE task.
To get the second one, don’t include any of the options.
So in SAS PROC TRANSPOSE does both jobs of split by and transpose interesting!
Thank you!
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.