topic Comma Delimited Variable Values (need to parse and create additional Obs) in SAS Programming

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

JayS — Mon, 15 Nov 2021 15:07:53 GMT

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

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

maguiremq — Mon, 15 Nov 2021 15:40:11 GMT

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

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

JayS — Mon, 15 Nov 2021 15:49:17 GMT

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

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

JayS — Mon, 15 Nov 2021 16:17:17 GMT

Thank you, works perfect...

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

Thanks Again... Jay