How to Split Observations into Separate Columns

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

How to Split Observations into Separate Columns

[ Edited ]

***Edited for clarification***

 

Hello! I am using SAS ver9.3 and would like to split observations into two columns. Here's what I am looking for:

 

Group      Column 1      Column 2      Column 3

   001            NT1                NT1

   001            NT2                NT2

   001            T1                                         T1

   001            T2                                         T2

   001            T3                                         T3

   002            NT1                NT1

   002            T1                                         T1

   002            T2                                         T2

   003            NT1                NT1

   003            NT2                NT2

   003            T1                                         T1

 

Is it possible to split observations like so in columns 2 and 3? They share a group ID.

 

I am a beginner at SAS programming, so I apologize if there is a simple solution I am overlooking.

 

 

Thank you!


Accepted Solutions
Solution
‎05-15-2017 06:37 PM
Grand Advisor
Posts: 17,396

Re: How to Split Observations into Separate Columns

To code a solution you need to specify the rules. 

So what are the rules? If you provide that, you're likely to get an answer much faster.

 

So, it looks like NT goes to one column and T values go to a different column? Does that sound right? 

If so, we could expand the rule even. Obs that start with N go to column 2 and obs that start with T go to column 3. 

 

To me the next questions become, well, how do I tell when one starts with NT or T?

 

So lets break that down into pseudo code:

 

Get first character

If N then assign to variable2

if T then assign to variable3

Else ... -> no other rules specified. 

 

Creating the code from here is now a fill in the blank exercise in terms of SAS coding. Assuming my logic is correct. So I'll wait on your confirmation before I bother to type out the code but you should also give it a shot yourself. 

 

To obtain the first character use SUBSTR() function. 

View solution in original post


All Replies
Trusted Advisor
Posts: 1,500

Re: How to Split Observations into Separate Columns

[ Edited ]

yawenyu wrote:

Hello! I am using SAS ver9.3 and would like to split observations into two columns. Here's what I am looking for:

 

Column 1                                    Column 1      Column 2

T1                                                T1                   NT1

T2                                                T2                   NT2

T3                        to                     T3                   NT3

NT1

NT2

NT3

 

I am a beginner at SAS programming, so I apologize if there is a simple solution I am overlooking.

 

 

Thank you!


I'm afraid I find this problem to be incompletely specified. Art gives an answer that works ONLY if there are the same number of T and NT, and not otherwise.

 

Typically, there is some ID variable and group variable that allows this sort of splitting of columns, such as:

 

ID      Group       Value
1001      A           T1
1002      A           T2
1009      A           T3
1001      B           NT1
1002      B           NT2
1009      B           NT3

 

 

and then the problem is completely well specified and we can give you a general answer that should work in all cases, but your problem as stated does not have a general answer that works in all cases.

Occasional Contributor
Posts: 12

Re: How to Split Observations into Separate Columns

Hi Paige,

 

Thanks for replying. It's true that there isn't the same number of T and NT as you said, and they are attached to a group variable. Please let me give a better example:

 

Group      Column 1      Column 2      Column 3

   001            NT1                NT1

   001            NT2                NT2

   001            T1                                         T1

   001            T2                                         T2

   001            T3                                         T3

   002            NT1                NT1

   002            T1                                         T1

   002            T2                                         T2

   003            NT1                NT1

   003            NT2                NT2

   003            T1                                         T1

 

Is it possible to split observations like so in columns 2 and 3?

 

 

Thank you,

Yawen

Solution
‎05-15-2017 06:37 PM
Grand Advisor
Posts: 17,396

Re: How to Split Observations into Separate Columns

To code a solution you need to specify the rules. 

So what are the rules? If you provide that, you're likely to get an answer much faster.

 

So, it looks like NT goes to one column and T values go to a different column? Does that sound right? 

If so, we could expand the rule even. Obs that start with N go to column 2 and obs that start with T go to column 3. 

 

To me the next questions become, well, how do I tell when one starts with NT or T?

 

So lets break that down into pseudo code:

 

Get first character

If N then assign to variable2

if T then assign to variable3

Else ... -> no other rules specified. 

 

Creating the code from here is now a fill in the blank exercise in terms of SAS coding. Assuming my logic is correct. So I'll wait on your confirmation before I bother to type out the code but you should also give it a shot yourself. 

 

To obtain the first character use SUBSTR() function. 

Trusted Advisor
Posts: 1,500

Re: How to Split Observations into Separate Columns


Reeza wrote:

To code a solution you need to specify the rules. 

So what are the rules? If you provide that, you're likely to get an answer much faster.

 

So, it looks like NT goes to one column and T values go to a different column? Does that sound right? 

If so, we could expand the rule even. Obs that start with N go to column 2 and obs that start with T go to column 3. 



I was going to say the same thing, until I read what @Reeza wrote.

 

The OP has not specified the rules, and although I can guess (as Reeza did), far to often I either guess wrong, or the OP comes back and says he left something out, as we have already seen.

 

So, Mr or Miss @yawenyu, tell us what the rules are, please!

Occasional Contributor
Posts: 12

Re: How to Split Observations into Separate Columns

Hi Reeza,

 

Thanks for the reply. I went ahead and tried your pseudo code, and using the substr function did the trick.

 

 

NewVar=substr(Column1,1,1);

if NewVar='T' then Column2=Column1;
else Column2=' ';

if NewVar='N' then Column3=Column1;
else Column3=' ';

 

Apologies if my question was a bit vague. I am quite new to programming, especially with SAS.

Valued Guide
Posts: 763

Re: How to Split Observations into Separate Columns

Hi, if you want to try something else ...

 

data want;
set have;
column2 = ifc(column1 eq : 'N', column1, " ");
column3 = ifc(column1 eq : 'T', column1, " ");
run;

 

 

☑ This topic is SOLVED.

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

Discussion stats
  • 6 replies
  • 186 views
  • 3 likes
  • 4 in conversation