BookmarkSubscribeRSS Feed
ganeshdh
Calcite | Level 5

Hi all,

 

Can someone please help with this?

 

I want to convert the below table to

 

A1
a3
b1
a5
c21
c4
d3
b2
b5

 

Output:

abcd
11213
324 
55  

 

Thanks Heaps!

5 REPLIES 5
Reeza
Super User

Try PROC TRANSPOSE. 

If you have difficulty please post your code and log, with an explanation of what is not working. 

 

EDIT: note that you'll need to make them all the values in the first column the same case, and you can use upcase/lowcase to standardize the values.

 


@ganeshdh wrote:

Hi all,

 

Can someone please help with this?

 

I want to convert the below table to

 

A 1
a 3
b 1
a 5
c 21
c 4
d 3
b 2
b 5

 

Output:

a b c d
1 1 21 3
3 2 4  
5 5    

 

Thanks Heaps!


 

ganeshdh
Calcite | Level 5

Hi Reeza,

 

I want to remove the duplicates for my output table column names and want to make their corresponding values as columns.

 

proc sql;
create table DSC.Team as
select TEAM,
from DSC.Scorecard
order by Team;
run;

 

proc sort data= DSC.TEAM NODUPKEY;
by TEAM;
run;

 

proc transpose data= DSC.Team
out= DSC.Team_Trans;
ID Team;
run;

 

Here I have made the first row to column names and I am just wondering how to populate the table with its corresponding values

 

Thanks!

Reeza
Super User

@ganeshdh wrote:

Hi Reeza,

 

I want to remove the duplicates for my output table column names and want to make their corresponding values as columns.

 

 


 

Based on your example, you need two steps. 

1. Make all the case the same, i.e. A and a are different values according to SAS. 

2. Use a PROC TRANSPOSE on the data. 

 

The approach you're trying will work but it's more work and includes unnecessary steps which SAS will take care of. In your current PROC TRANSPOSE you don't specify a VAR to include. 

Astounding
PROC Star

Because you have multiple observations for each TEAM, as well as spelling differences, you will need multiple steps here.

 

data temp;

set have;

team = upcase(team);

run;

 

proc sort data=temp;

by team;

run;

 

data temp2;

set temp;

by team;

if first.team then group = 1;

else group + 1;

run;

 

proc sort data=temp2;

by group;

run;

 

proc transpose data=temp2 out=want (drop=_name_);

var value;  /* or whatever the variable name is */

id team;

by group;

run;

 

It is conceivable that you would want to add GROUP to the list of variables being dropped.

Ksharp
Super User

use MERGE skill proposed by Me,Arthur.T,Matt

http://support.sas.com/resources/papers/proceedings15/2785-2015.pdf

 

data have;
input x $ y;
cards;
a	1
a	3
b	1
a	5
c	21
c	4
d	3
b	2
b	5
;
run;
proc sql;
select distinct catt('have(where=(x="',x,'") rename=(y=',x,'))') into : list separated by ' '
 from have;
quit;
data want;
 merge &list;
 drop x;
run;

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

Creating Custom Steps in SAS Studio

Check out this tutorial series to learn how to build your own steps in SAS Studio.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 1492 views
  • 0 likes
  • 4 in conversation