topic Re: how to read the variable that has 3 codesor more.. in SAS Programming

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

BaalaRaaji — Mon, 13 Aug 2018 02:36:34 GMT

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

….

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

novinosrin — Mon, 13 Aug 2018 03:39:23 GMT


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;

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

PGStats — Mon, 13 Aug 2018 03:40:54 GMT

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

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

novinosrin — Mon, 13 Aug 2018 03:43:55 GMT


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;

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

Patrick — Mon, 13 Aug 2018 04:01:11 GMT

@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;

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

BaalaRaaji — Mon, 13 Aug 2018 04:04:22 GMT

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

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

BaalaRaaji — Mon, 13 Aug 2018 04:08:12 GMT

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...

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

Ksharp — Mon, 13 Aug 2018 13:50:32 GMT

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