topic Re: How to Split Observations into Separate Columns in SAS Programming

How to Split Observations into Separate Columns

yawenyu — Mon, 15 May 2017 21:02:17 GMT

***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!

Re: How to Split Observations into Separate Columns

PaigeMiller — Mon, 15 May 2017 19:16:06 GMT

@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.

Re: How to Split Observations into Separate Columns

yawenyu — Mon, 15 May 2017 21:01:08 GMT

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

Re: How to Split Observations into Separate Columns

Reeza — Mon, 15 May 2017 21:16:17 GMT

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.

Re: How to Split Observations into Separate Columns

PaigeMiller — Mon, 15 May 2017 21:39:34 GMT

@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!

Re: How to Split Observations into Separate Columns

yawenyu — Mon, 15 May 2017 22:37:06 GMT

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.

Re: How to Split Observations into Separate Columns

MikeZdeb — Tue, 16 May 2017 02:29:01 GMT

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;