SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

creating one continuous column from multiple rows

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

creating one continuous column from multiple rows

Good day! My data looks like this:

ID      B1 B2 B3

1        1   2   3

2        4   5   6             

3        7   8   9

 

I would like it to look like this:

ID    B# Content

1       1      1

1       2      2

1       3      3

2       1      4

2       2      5

2       3      6

3       1      7

3       2      8

3       4      9

 

Could you pls help me. Thanks!


Accepted Solutions
Solution
‎03-17-2017 11:11 AM
PROC Star
Posts: 307

Re: creating one continuous column from multiple rows

In addition to transposing, you could do it in a data step. I assume your last row is a typo, though. I don't know where the b# = 4 would come from.

 

data have;
    input ID B1 B2 B3;
datalines;
1 1   2   3
2 4   5   6
3 7   8   9
;

data want(drop = b:);
    set have;
    array b{3};
    do num_b = 1 to 3;
        content = b(num_b);
        output;
    end;
run;

View solution in original post


All Replies
Super User
Super User
Posts: 7,955

Re: creating one continuous column from multiple rows

Without test data in the form of a datastep, this is just a guess:

proc transpose data=have out=want;
  by id;
  var b:;
run;
Solution
‎03-17-2017 11:11 AM
PROC Star
Posts: 307

Re: creating one continuous column from multiple rows

In addition to transposing, you could do it in a data step. I assume your last row is a typo, though. I don't know where the b# = 4 would come from.

 

data have;
    input ID B1 B2 B3;
datalines;
1 1   2   3
2 4   5   6
3 7   8   9
;

data want(drop = b:);
    set have;
    array b{3};
    do num_b = 1 to 3;
        content = b(num_b);
        output;
    end;
run;
New Contributor
Posts: 3

Re: creating one continuous column from multiple rows

Posted in reply to collinelliot

thank you so much! the last one was a typo 

PROC Star
Posts: 307

Re: creating one continuous column from multiple rows

You're welcome. @RW9' s solution would probably work for you, too, and you should probably get familiar with how proc transpose works since there are times when it will be far easier than a data step approach. 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 217 views
  • 3 likes
  • 3 in conversation