BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Barney1998
Obsidian | Level 7

Hallo to everyone,

I have this problem to overcome. I have a data set which their information from some columns are repeated. For example like:
DATA Input; INPUT Col1 $ Col2 $ Col3 $ Col4 $ Col5 $ Col6 $ Col7;

DATALINES;

A X X S F 1  10

A X X S F 2 20

A X X S F 3 30

B Y R S Z 1 20

B Y R S Z 2 40 ;

 RUN;

 

and i want to hold all the info in one row like

DATA Input; INPUT Col1 $ Col2 $ Col3 $ Col4 $ 1 $ 2 $ 3;

DATALINES;

A X X S F  10 20 30

B Y R S Z  20 30  -

 RUN;

what should i do?

 

1 ACCEPTED SOLUTION

Accepted Solutions
FreelanceReinh
Jade | Level 19

Hallo @Barney1998,

 

Try this:

data have;
input (col1-col5) ($) col6 col7;
datalines;
A X X S F 1 10
A X X S F 2 20
A X X S F 3 30
B Y R S Z 1 20
B Y R S Z 2 40
A B C D E 3 13
A B C D E 2 22
;

proc transpose data=have out=want(drop=_name_) prefix=somename;
by col1 col2 col3 col4 col5 notsorted; /* shorter: col1-col5 */
id col6;
var col7;
run;

If your real HAVE dataset is sorted (rather than only grouped) by COL1-COL5, you can omit the NOTSORTED keyword in the BY statement. COL6 may also be a character variable, but must not contain duplicate values within a BY group.

View solution in original post

4 REPLIES 4
PaigeMiller
Diamond | Level 26

Please explain the logic (Step-by-step, leaving nothing out) behind this re-arranging of the data.

--
Paige Miller
FreelanceReinh
Jade | Level 19

Hallo @Barney1998,

 

Try this:

data have;
input (col1-col5) ($) col6 col7;
datalines;
A X X S F 1 10
A X X S F 2 20
A X X S F 3 30
B Y R S Z 1 20
B Y R S Z 2 40
A B C D E 3 13
A B C D E 2 22
;

proc transpose data=have out=want(drop=_name_) prefix=somename;
by col1 col2 col3 col4 col5 notsorted; /* shorter: col1-col5 */
id col6;
var col7;
run;

If your real HAVE dataset is sorted (rather than only grouped) by COL1-COL5, you can omit the NOTSORTED keyword in the BY statement. COL6 may also be a character variable, but must not contain duplicate values within a BY group.

Barney1998
Obsidian | Level 7

Thank you so much!

ballardw
Super User

@Barney1998 wrote:

Hallo to everyone,

I have this problem to overcome. I have a data set which their information from some columns are repeated. For example like:
DATA Input; INPUT Col1 $ Col2 $ Col3 $ Col4 $ Col5 $ Col6 $ Col7;

DATALINES;

A X X S F 1  10

A X X S F 2 20

A X X S F 3 30

B Y R S Z 1 20

B Y R S Z 2 40 ;

 RUN;

 

and i want to hold all the info in one row like

DATA Input; INPUT Col1 $ Col2 $ Col3 $ Col4 $ 1 $ 2 $ 3;

DATALINES;

A X X S F  10 20 30

B Y R S Z  20 30  -

 RUN;

what should i do?

 


Where does the 30 on the row starting with B in your Want data come from. There isn't any 30 in the starting data.

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


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
  • 4 replies
  • 796 views
  • 2 likes
  • 4 in conversation