BookmarkSubscribeRSS Feed
Feyng819
Obsidian | Level 7
So I have a data set with 3 variables, I want to remove the dash symbol and split the data into 4 rows of data when var2 contains number and var3 contains a letter., and var2 will be under var1 after splitting. For example “1 -2 C” will be split into:
var1 var2 var3
1 . .
1 . C
2 . .
2 . C

and split the data into 2 rows of data if var2 contains number and var3 does not contain anything. For example “3 4 .” Will be split into:
3 . .
4 . .

Other data will remain the same.

Here is my data:
Data have;
Input var1 $ var2 $ var3 $20.;
Cards;
1 -2 C
3 4 .
7 -9 F
10 . .
12 . H
15 B .
run;

And my expected output is:
var1 var2 var3
1 . .
1 . C
2 . .
2 . C
3 . .
4 . .
7 . .
7 . F
9 . .
9 . F
10 . .
12 . H
15 B .

I can get “3 4 .” into
3 . .
4 . .
But I cannot figure out how to split the data into 4 for the condition above. Thank you!!
1 REPLY 1
PGStats
Opal | Level 21

Start with this:

 

data want;
set have;
v2 = compress(var2, "-");
if anydigit(v2) and not anyalpha(trimn(v2)) then do;
    if missing(var3) then do;
        call missing(var2);
        output;
        var1 = v2;
        output;
        end;
    else do;
        call missing(var2);
        v3 = var3;
        call missing(var3);
        output;
        var3 = v3;
        output;
        var1 = v2;
        call missing(var3);
        output;
        var3 = v3;
        output;
        end;
    end;
else output;
drop v2 v3;
run;

 

PG

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 553 views
  • 0 likes
  • 2 in conversation