topic Table transformation/reporting in SAS Programming

Table transformation/reporting

staplegunjelly — Sun, 25 Feb 2024 22:18:03 GMT

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?

Re: Table transformation/reporting

Patrick — Sun, 25 Feb 2024 22:51:22 GMT

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.

Re: Table transformation/reporting

staplegunjelly — Sun, 25 Feb 2024 23:32:18 GMT

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
;

Re: Table transformation/reporting

Patrick — Mon, 26 Feb 2024 01:12:09 GMT

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;

Re: Table transformation/reporting

Ksharp — Mon, 26 Feb 2024 04:35:43 GMT

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;