Help using Base SAS procedures

Extract new ID from records with one hyphen

Accepted Solution Solved
Reply
Contributor
Posts: 33
Accepted Solution

Extract new ID from records with one hyphen

Hi there

I have a data set with a variable "ID" shown below and would like to create a new ID variable as shown below in column "New ID" (need to get rid of the hyphen and numbers after the hyphen). Please notice that the ID changes ONLY when ONE hyphen is present in variable "ID".

IDNew ID
1170192-011170192
1171775-011171775
1175345-011175345
12-06-021912-06-0219
12-06-022312-06-0223
12-06-023512-06-0235
12-06-023612-06-0236
12-06-026312-06-0263
13-06-029113-06-0291
13-06-029413-06-0294
13-06-032113-06-0321
14-06-034714-06-0347
14-06-035114-06-0351
14-06-059714-06-0597
15-06-011615-06-0116
15-06-035315-06-0353
15-06-036515-06-0365
15-06-036715-06-0367
15-06-036915-06-0369
15-06-037115-06-0371
99-06-992199-06-9921
SP0100470-SP0100470
SP080004-0SP080004
SP080005-0SP080005

All help will be appreciated it

Thank you


Accepted Solutions
Solution
‎07-09-2015 03:32 PM
Valued Guide
Posts: 858

Re: Extract new ID from records with one hyphen

Here you go:

DATA have;

infile cards dsd;

informat id $10.;

format id $10.;

input id;

cards;

1170192-01

1171775-01

1175345-01

12-06-0219

12-06-0223

12-06-0235

12-06-0236

12-06-0263

13-06-0291

13-06-0294

13-06-0321

14-06-0347

14-06-0351

14-06-0597

15-06-0116

15-06-0353

15-06-0365

15-06-0367

15-06-0369

15-06-0371

99-06-9921

SP0100470-

SP080004-0

SP080005-0

;

run;

data want;*(rename=(new_id=id));

set have;

count = countc(id,'-');

if count = 1 then do;

New_ID = scan(id,1,'-');

end;

if count ne 1 then do;

New_ID = ID;

end;

/*drop id count;*/

run;

After you run it and see what the dataset looks like uncomment the few things that are commented and see if that is the desired output.

View solution in original post


All Replies
Solution
‎07-09-2015 03:32 PM
Valued Guide
Posts: 858

Re: Extract new ID from records with one hyphen

Here you go:

DATA have;

infile cards dsd;

informat id $10.;

format id $10.;

input id;

cards;

1170192-01

1171775-01

1175345-01

12-06-0219

12-06-0223

12-06-0235

12-06-0236

12-06-0263

13-06-0291

13-06-0294

13-06-0321

14-06-0347

14-06-0351

14-06-0597

15-06-0116

15-06-0353

15-06-0365

15-06-0367

15-06-0369

15-06-0371

99-06-9921

SP0100470-

SP080004-0

SP080005-0

;

run;

data want;*(rename=(new_id=id));

set have;

count = countc(id,'-');

if count = 1 then do;

New_ID = scan(id,1,'-');

end;

if count ne 1 then do;

New_ID = ID;

end;

/*drop id count;*/

run;

After you run it and see what the dataset looks like uncomment the few things that are commented and see if that is the desired output.

Contributor
Posts: 33

Re: Extract new ID from records with one hyphen

THANK YOU VERY MUCH MARK!!

Super User
Posts: 5,083

Re: Extract new ID from records with one hyphen

This is a good set of tools to use here, but you might want to simplify things a bit:

data want;

set have;

if countc(id, '-') = 1 then id = scan(id, 1, '-');

run;

Contributor
Posts: 33

Re: Extract new ID from records with one hyphen

Thanks again Mark,

how should I modify the code if I want to keep in one separated column the hyphen and numbers?, this column for the fist three cases will have -01

Eduardo.

Valued Guide
Posts: 858

Re: Extract new ID from records with one hyphen

Here's what you would want:

data want;

set have;

count = countc(id,'-');

if count = 1 then do;

New_ID = scan(id,1,'-');

end;

if count ne 1 then do;

New_ID = ID;

end;

drop count;

run;

The changes I made, remove the rename statement from the first line, we won't be renaming anything, and I'm only dropping the count at the bottom, I used that to identify what has more than one hyphens. 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 336 views
  • 3 likes
  • 3 in conversation