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 Kicks Off on June 11!

Watch the live Hackathon Kickoff to get all the essential information about the SAS Hackathon—including how to join, how to participate, and expert tips for success.

YouTube LinkedIn

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