topic Re: How to delimit a string variable in dataset in SAS Procedures

How to delimit a string variable in dataset

promo_at_work — Tue, 14 Aug 2018 00:40:37 GMT

Hi,

I have a dataset where the only variable is a long string delimited by "|"

An example of a record would be:

A||032345|99|||

I've tried some solutions that I've encountered online, but the problem is when you encounter the delimiters without anything between them. They just end up being ignored. Here's an example of code I've used.

data want (drop=i); set have;
  array parsed_vars $ new_var1-new_var%eval(&maxelements);
  do i = 1 to &maxelements;
  parsed_vars{i} = scan(orig_var,i,'.');
  end;
run;

I'd like the parsing output to appear as

orig_var	new_var1	new_var2	new_var3	new_var4	new_var5	new_var6
A\|\|032345\|99\|\|\|	A	.	032345	99	.	.

However, the output appears as:

orig_var	new_var1	new_var2	new_var3	new_var4	new_var5	new_var6
A\|\|032345\|99\|\|\|	A	032345	99	.	.	.

I'm not sure how to fix it and would appreciate any help. If possible, I'd prefer to use proc sql if it can be done that way.

Thanks

Re: How to delimit a string variable in dataset

Haikuo — Tue, 14 Aug 2018 01:41:43 GMT

Do please RTM. Scan() has lots of options.

data test;
ori='A||032345|99|||';
array var(6) $10;
do i=1 to 6;
var(i)=scan(ori,i,'|','m');
end;
run;

Re: How to delimit a string variable in dataset

promo_at_work — Tue, 14 Aug 2018 05:04:40 GMT

Perfect! Thank you so much!

Re: How to delimit a string variable in dataset

ballardw — Tue, 14 Aug 2018 16:10:27 GMT

I would likely go back to the original file, probably use Proc Import with delimiter='|' getnames=No and guessingrows=max.

The default out of import will be to treat two delimiters to indicate a missing value. I believe the variable names would be var1-varn