Hello Everyone!
I have a large table which looks like this:
And I would like to transform it into something like this:
What's the best way to achieve this?
What does "large" mean? How many rows do you have?
Look into Proc Report.
If you'd like to get a code example from us then please provide sample data in usable form - certainly not as a screenshot, best via SAS data step code that creates the sample data.
I'm sorry. I meant to say long table. Its the same two variables but much more values for each. I basically want to display it in the way I showed in the post. Here's the sample data:
data have;
input equipment $ voltage $;
datalines;
A 13KV
B 23KV
C 115KV
D 115KV
E 230KV
F 230KV
G 13KV
H 13KV
;
Actually... Proc Report is normally used to print statistics in the cells like counts, sums etc. Not sure how I could just print the source values.
Below closest I could come-up with to create your desired layout without the need for some cumbersome coding. May-be someone else got a better idea how to address this.
data have;
input equipment $ voltage $;
datalines;
A 13KV
B 23KV
C 115KV
D 115KV
E 230KV
F 230KV
G 13KV
H 13KV
;
proc sort data=have out=inter;
by equipment;
run;
proc transpose data=inter out=want(drop=equipment) let name=Voltage;
by equipment;
id voltage;
var equipment;
run;
proc print data=want noobs;
run;
data have;
input equipment $ voltage $;
datalines;
A 13KV
B 23KV
C 115KV
D 115KV
E 230KV
F 230KV
G 13KV
H 13KV
;
proc sql noprint nowarn;
select distinct catt('have(rename=(equipment=',nliteral(voltage),') where=(voltage="',voltage,'"))') into :merges separated by ' '
from have
order by input(compress(voltage,,'kd'),best.);
quit;
options validvarname=any;
data want;
length voltage $ 40;
merge &merges.;
voltage='Equipment';
run;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.