topic Re: Split field into 2 field in SAS Programming

Split field into 2 field

Ronein — Thu, 26 Dec 2019 04:54:54 GMT

Hello

I want to split field "Customer_Branch" into two fields: "Customer" and "branch".

All digits left to minus sign will be in "Customer" field

All digits right to minus sign will be in "branch" field.

What is the way to do it please?

Data tbl;
input Customer_Branch $30.;
cards;
2345777-783
3444-965
476-814
47474744-981
;
run;

Re: Split field into 2 field

Ronein — Thu, 26 Dec 2019 05:04:35 GMT

I found nice solution.

Is there another nice way to do it?

Data tbl;
input Customer_Branch $30.;
cards;
2345777-783
3444-965
476-814
47474744-981
;
run;
data tbl2;
set tbl;
Customer=substr(Customer_Branch, 1, find(Customer_Branch, '-')-1);
Branch=substr(Customer_Branch, length(Customer)+2, length(Customer_Branch)-length(Customer)-1);
run;

Re: Split field into 2 field

KachiM — Thu, 26 Dec 2019 05:30:49 GMT

@Ronein

Another way is to use 'DLM'. Here is it:

Data tbl;
infile datalines dlm = '-';
input Customer Branch ;
datalines;
2345777-783
3444-965
476-814
47474744-981
;
run;

Re: Split field into 2 field

SASKiwi — Thu, 26 Dec 2019 06:52:37 GMT

Another way:

Data tbl;
input Customer_Branch $30.;
cards;
2345777-783
3444-965
476-814
47474744-981
;
run;
data tbl2;
set tbl;
Customer=scan(Customer_Branch, 1, '-');
Branch=scan(Customer_Branch, 2, '-');
run;

Re: Split field into 2 field

sustagens — Thu, 26 Dec 2019 08:58:10 GMT

data want;
set tbl;
Customer=substr(Customer_Branch,1,find(Customer_Branch,'-')-1);
Branch=substr(Customer_Branch,find(Customer_Branch,'-')+1);
run;

Re: Split field into 2 field

Kurt_Bremser — Thu, 26 Dec 2019 09:35:53 GMT

This is the classic case for the scan() function, see @SASKiwi

KISS (Maxim 29)

Re: Split field into 2 field

ed_sas_member — Thu, 26 Dec 2019 10:27:00 GMT

Hi @Ronein

You can use the scan function, which is the easiest way to do this job in your case.

It splits your string in several parts ("words") according to a delimiter that you specify as the third argument (in your case, an hyphen)

You can then retrieve the first, the second, ... word.

data tbl2;
	set tbl;
	Customer = scan(Customer_Branch,1,"-");
	Branch = scan(Customer_Branch,2,"-");
run;