***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!
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.
@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.
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
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.
@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!
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.