BookmarkSubscribeRSS Feed
Ruolin
Calcite | Level 5

Hi all, I am having a problem getting a table in a way I want it to look like.

For example,

my table is the following,

ID_number  SUB_number

ID1                 SUB1

ID1                 SUB2

ID2                 SUB3

I am wondering if there is a query that can transform my table into the following

ID_number     SUB_number       SUB_number2

ID1                   SUB1                    SUB2

ID2                   SUB3

( SUB1 and SUB2 are associated with the same ID1)

Please advise.

3 REPLIES 3
mohamed_zaki
Barite | Level 11

data have;

input a $ b $;

cards;

ID1 SUB1

ID1 SUB2

ID2 SUB3

ID3 SUB5

;

run;

proc transpose data=have out=new (drop=_name_);

by a;

var b;

run;

Patrick
Opal | Level 21

Just a little variation to above creating variable names closer to your output data set

data have;

  input ID_number $ sub_number $;

  cards;

ID1 SUB1

ID1 SUB2

ID2 SUB3

ID3 SUB5

;

run;

proc transpose data=have out=new (drop=_:) prefix=SUB_Number;

  by id_number;

  var sub_number;

run;

Loko
Barite | Level 11

Hello,

A lot of questions have been asked related to this topic. Just search for "Transpose" within the search facility of the forum and you may find a lot of interesting and useful answers (besides the ones you have already got on your post).

Here is one example:

https://communities.sas.com/message/206611#206611

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

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.

SAS Training: Just a Click Away

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

Browse our catalog!

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