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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

6 REPLIES 6
PaigeMiller
Diamond | Level 26

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

--
Paige Miller
yawenyu
Obsidian | Level 7

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

Reeza
Super User

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. 

PaigeMiller
Diamond | Level 26

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

--
Paige Miller
yawenyu
Obsidian | Level 7

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.

MikeZdeb
Rhodochrosite | Level 12

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;

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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