Comma Delimited Variable Values (need to parse and create additional Obs)

[JayS]

I have a dataset where the Obs have variables that looks like this:

Var1              Var2                              Var3

121212        5678,2356,2312             '5678',2356',2312'  // Note single ticks in Var3

Var1 is the primary key to merge on, and character.

However I need to also parse through EITHER Var2 OR Var3 to merge with those values.

Basically, I need to create additional rows (In this case 3) to set up the dataset for another Merge pass.

How best to 'Flip' either Var2 or Var3, generating 3 more obs?

TIA, Jay

SAS EG: 7.15 HFS

[maguiremq · Accepted solution]

Are the single ticks intentional in their form? In other words, did you mean to encapsulate each numeric grouping in single quotes ('5678', '2356', '2312') or are they actually like this ('5678', 2356', 2312')?

Also, what makes you choose `var2` or `var3`? Do you have some conditional reason? Are they always the same?

Here is how you can output three observations based on your supplied data:

data want;
var1 = 121212;
var2 = "5678,2356,2312";
/*var3 = "'5678',2356',2312'";*/
do i = 1 to countw(var2, ",");
	var2_i = scan(var2, i, ",");
	output;
end;
drop var2 i;
run;

Obs var1 var2_i 
1 121212 5678 
2 121212 2356 
3 121212 2312 

[JayS]

Thanks, I'll take a look...

Best I can figure is when they created the variables they created one with with ticks and one without, no idea why, maybe passing into SQL Statement?

Based on your reply, I'm going to use Var2..

Thanks for your reply...

Jay

[JayS]

Thank you, works perfect...

I'll share with balance of the team as others will be processing the data at times.

Thanks Again... Jay