topic Re: consecutive numbers in to one column, and the rest characters in the other in SAS Programming

consecutive numbers in to one column, and the rest characters in the other

J_J_J — Wed, 03 Nov 2021 14:13:36 GMT

Hi!

I want to put 7 consecutive numbers in to one column, and the rest characters in the other. Please let me know how to do it?

data state;

input ID $;

cards;

AAAAAA BBB 1111111
2222222 CCC -1111
CCCCCCC 2222222
CCCCCCCCCCC 3333333

;

run;

Want:

ID	Number	ABC
AAAAAA BBB 1111111	1111111	AAAAAA BBB
2222222 CCC -1111	2222222	CCC -1111
CCCCCCC 2222222	2222222	CCCCCCC
CCCCCCCCCCC 3333333	3333333	CCCCCCCCCCC

Re: consecutive numbers in to one column, and the rest characters in the other

ballardw — Wed, 03 Nov 2021 14:24:47 GMT

Suggest strongly that you test code to build data sets before posting it. Your code had no actual values with both the 7 digits and characters.

Try this one:

data state;
  infile datalines dlm=',';
  input ID :$20.;
cards;
AAAAAA BBB 1111111
2222222 CCC -1111
CCCCCCC 2222222
CCCCCCCCCCC 3333333
;

The default length when reading data with a simple $ is 8 characters, so couldn't hold the length you desired. Second spaces delimit the values, so only the first set of characters were read. Specifying the DLM=',' means that a comma is needed to delimit values, so all of the string can be read. The :$20. says to use a character informat of upto 20 characters to read (since that is the longest value you show).

Re: consecutive numbers in to one column, and the rest characters in the other

AMSAS — Wed, 03 Nov 2021 15:07:05 GMT

Once you have corrected your sample data issues, then you are probably going to want to do something like this, using the PRX functions

/* Create Sample Data */
data state;
  infile datalines dlm=',';
  input ID :$20.;
cards;
AAAAAA BBB 1111111
2222222 CCC -1111
CCCCCCC 2222222
CCCCCCCCCCC 3333333
;

data want ;
	set state ;
	/* create a pattern ID that looks for 7 consecutive numbers */
	patternID=prxparse('/[0-9]{7}/') ;
	/* Search for that pattern */
	position=prxmatch(patternID, id);
	put _n_= position= ;
run ;

Re: consecutive numbers in to one column, and the rest characters in the other

PeterClemmensen — Wed, 03 Nov 2021 18:27:46 GMT

Try this

data state;
input ID $ 1-50;
cards;
AAAAAA BBB 1111111
2222222 CCC -1111
CCCCCCC 2222222
CCCCCCCCCCC 3333333
;

data want;
   set state;
   number = substr(ID, prxmatch('/\d{7}/', ID), 7);
   ABC = prxchange('s/\d{7}//', -1, ID);
run;

Result:

ID                 number   ABC
AAAA BBB 1111111   1111111  AAAAAA BBB
22222 CCC -1111    2222222  CCC -1111
CCCCC 2222222      2222222  CCCCCCC
CCCCCCCCC 3333333  3333333  CCCCCCCCCCC

Re: consecutive numbers in to one column, and the rest characters in the other

J_J_J — Fri, 05 Nov 2021 08:08:58 GMT

PeterClemmensen, thank you so much!