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

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

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
  • 576 views
  • 0 likes
  • 2 in conversation