how to read the variable that has 3 codesor more..

[BaalaRaaji]

Hi Guys,

I have a below sample data in excel that I import into sas..

Code	Code1
0122	WESXQI3C, BOUX17Q9, WESXYBWF
4512	DENX9UPH; BLUX8Z3F; DENXDQQ3
2183	MOUNTADA,   BROWND01, BROWND03
0122	CLARD
0130	BAXXB3OQ, BAXXKEQD BAXXEIAG
0130	HUNTPA1
5221	MERXKIEI, MERXDW49,
2183	LANX8M83, PROXEAKO, PROXEXMK, AABXJ2G7
4611	WEBXA56H, GERXDQN9 AEWEB GERXERSG JOYWEB
5251	DAWXR5D1

I have a master dataset to join on the code1 that has only one code indivually...so is there any function or sas code that I can run and create another dataset that has only one code1 list...i.e., to break them..

I can use SCAN but the delimiters are , and ; and & and space...all different as they entered manually in the excel data...

appreciate your advice and thoughts...

simple to get dataset like below...

code1

WESXQI3C

BOUX17Q9

WESXYBWF

DENX9UPH

BLUX8Z3F

DENXDQQ3

MOUNTADA

BROWND01

BROWND03

CLARD

BAXXB3OQ

BAXXKEQD

BAXXEIAG

HUNTPA1

MERXKIEI

MERXDW49

LANX8M83

PROXEAKO

PROXEXMK

AABXJ2G7

….

[novinosrin]

data have;
infile cards truncover;
input Code $	Code1 $100.;
cards4;
0122	WESXQI3C, BOUX17Q9, WESXYBWF
4512	DENX9UPH; BLUX8Z3F; DENXDQQ3
2183	MOUNTADA,   BROWND01, BROWND03
0122	CLARD
0130	BAXXB3OQ, BAXXKEQD BAXXEIAG
0130	HUNTPA1
5221	MERXKIEI, MERXDW49,
2183	LANX8M83, PROXEAKO, PROXEXMK, AABXJ2G7
4611	WEBXA56H, GERXDQN9 AEWEB GERXERSG JOYWEB
5251	DAWXR5D1
;;;;

data want;
set have;
k=char(code1,anypunct(code1));
k1=compress(code1,k);
do _n_=1 to countw(k1);
new_code=scan(k1,_n_);
output;
end;
keep code new_code;
run;

[novinosrin]

data want1;
set have;
k1=compress(code1,' ','kai');
do _n_=1 to countw(k1);
new_code=scan(k1,_n_);
output;
end;
keep code new_code;
run;

[BaalaRaaji]

Hi Novinosrin..

it works but for few scenario...like 'and'  spaces and comma without space is not working correctly with your code.

@Patrick code works exactly I wanted...later I can remove the numbers , and  symbols. Just cleaning the data...

Thank you Novinsrin and Patrick...genies...

[PGStats]

The third argument to the SCAN function can be a list of many possible delimiters, e.g. ",.; "

[Patrick · Accepted solution]

@BaalaRaaji

If you can't be sure what the delimiters are but you can be sure what they won't be (letter or digit) then you could use the scan() function with an exclusion list (=everything not listed to be treated as delimiter).

Below code demonstrates the approach:

data have;
  infile datalines4 truncover;
  input Code $  Code1 $100.;
  datalines4;
0122  WESXQI3C, BOUX17Q9, WESXYBWF
4512  DENX9UPH; BLUX8Z3F; DENXDQQ3
2183  MOUNTADA,   BROWND01, BROWND03
0122  CLARD
0130  BAXXB3OQ, BAXXKEQD BAXXEIAG
0130  HUNTPA1
5221  MERXKIEI, MERXDW49,
2183  LANX8M83, PROXEAKO, PROXEXMK, AABXJ2G7
4611  WEBXA56H, GERXDQN9 AEWEB GERXERSG JOYWEB
5251  DAWXR5D1
;;;;
run;

data want;
  set have;
  length single_code $20;
  do _i=1 by 1;
    single_code=scan(code1,_i,,'KAD');
    if missing(single_code) then leave;
    else output;
  end;
run;

[BaalaRaaji]

Thanks Patrick...that works amazingly...cheers!

[Ksharp]

data want;
  set have;
  do _i=1 to countw(code1,,'kad');
    single_code=scan(code1,_i,,'kad');
    output;
  end;
run;
proc print;run;