BookmarkSubscribeRSS Feed
staplegunjelly
Fluorite | Level 6

Hello Everyone!
I have a large table which looks like this:

staplegunjelly_0-1708898393427.png

And I would like to transform it into something like this:

staplegunjelly_1-1708898450759.png

What's the best way to achieve this?

4 REPLIES 4
Patrick
Opal | Level 21

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.

staplegunjelly
Fluorite | Level 6

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
;
Patrick
Opal | Level 21

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;

Patrick_0-1708909799428.png

 

Ksharp
Super User
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;

Ksharp_0-1708922135566.png

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 417 views
  • 0 likes
  • 3 in conversation