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

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
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.

SAS Training: Just a Click Away

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

Browse our catalog!

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