topic Re: How to remove digits from the observations? in SAS Programming

How to remove digits from the observations?

AbuChowdhury — Mon, 16 Mar 2015 18:33:00 GMT

Hi Experts,

The following is the sample of my dataset. I need to remove the first two and last two digits of "firms".

date firms

22/10/2008	AN8068571086
26/02/2010	AN8068571086
05/08/2011	AN8068571086
27/09/2011	AN8068571086
25/10/2011	AN8068571086
23/11/2011	AN8068571086
26/06/2012	AN8068571086

It means, now "firms" will be like 80685710.

Regards,

Abu

Re: How to remove digits from the observations?

Community_Help — Mon, 16 Mar 2015 19:14:36 GMT

Hi Abu, I was out on the communities today and noticed your question. I'm sure that a SAS expert will answer you soon. I happened to see this, just in case it helps:

Re: How to remove digits from the observations?

naveen_srini — Mon, 16 Mar 2015 19:21:13 GMT

Hi Abu,

data have;

input date ddmmyy10. firms :$15.;

datalines;

22/10/2008 AN8068571086

26/02/2010 AN8068571086

05/08/2011 AN8068571086

27/09/2011 AN8068571086

25/10/2011 AN8068571086

23/11/2011 AN8068571086

26/06/2012 AN8068571086

;

data want;

set have;

do i= 1 to length(firms);

new_firms=substr(firms,i,1);

if i in (1,2) or i=length(firms)-1 or i=length(firms) then continue;

group+1;

output;

end;

call missing(group);

run;

data final;

array a(15) $1 _temporary_;

call missing (of a{*});

do until(last.date);

set want;

by date;

a(group)=new_firms;

firm=cats(of a{*});

end;

keep date firm;

format date ddmmyy10.;

run;

HTH,

Naveen Srinivasan

L&T Infotech

Re: How to remove digits from the observations?

Astounding — Mon, 16 Mar 2015 19:46:39 GMT

It sounds like you are looking for a simple "chop off some characters":

firms = substr(firms, 3, 8);

If it is possible that number of characters changes from record to record, you could use:

firms = substr(firms, 3, length(firms)-4);

Good luck.

Re: How to remove digits from the observations?

Haikuo — Tue, 17 Mar 2015 01:40:00 GMT

data have;

input date ddmmyy10. firms :$15.;

datalines;

22/10/2008 AN8068571086

26/02/2010 AN8068571086

05/08/2011 AN8068571086

27/09/2011 AN8068571086

25/10/2011 AN8068571086

23/11/2011 AN8068571086

26/06/2012 AN8068571086

;

data want;

set have;

new_firms=prxchange('s/^\w\w|\w\w$//io',-1, strip(firms));

run;

Re: How to remove digits from the observations?

AbuChowdhury — Tue, 17 Mar 2015 12:34:42 GMT

Hi,

It is not working and shows the following messages:

NOTE: Numeric values have been converted to character values at the places given by:

(Line):(Column).

127:16

NOTE: Character values have been converted to numeric values at the places given by:

(Line):(Column).

127:9

Re: How to remove digits from the observations?

AbuChowdhury — Tue, 17 Mar 2015 12:48:31 GMT

Hi,

After running your codes, more observations are shown in the "Final" dataset than the original dataset. How to address this issue?

Re: How to remove digits from the observations?

Ksharp — Tue, 17 Mar 2015 13:02:44 GMT

data have;

input date ddmmyy10. firms :$15.;

datalines;

22/10/2008 AN8068571086

26/02/2010 AN8068571086

05/08/2011 AN8068571086

27/09/2011 AN8068571086

25/10/2011 AN8068571086

23/11/2011 AN8068571086

26/06/2012 AN8068571086

;

data want;

set have;

length want $ 20;

want=substr(firms,3,length(firms)-4);

run;

Re: How to remove digits from the observations?

art297 — Tue, 17 Mar 2015 13:04:22 GMT

Abu,

Both Astounding's and Haikuo's code should have worked. I think you will have to post an example of your data (in the form of a data step), and the code you ran that produced the notes, in order for anyone here to be able to help you.

Re: How to remove digits from the observations?

naveen_srini — Tue, 17 Mar 2015 13:47:22 GMT

I tested mine using the sample data you provided. It appeared to me the dataset is sorted by date. So, in the 1st datastep, the dataset gets flipped as each date duplicates forms a group. So here unless your real matches with your sample, i can't tell until you let us know what it really looks like. I just reran the same code that I gave you and that seems ok.

Either way, prx is perhaps the most ideal for character manipulation, so I'd suggest implement Haikuo's code.

Re: How to remove digits from the observations?

AbuChowdhury — Tue, 17 Mar 2015 14:20:54 GMT

I include the SAS file.I cannot attach the SAS file, but I have attached the CSV file.

Re: How to remove digits from the observations?

AbuChowdhury — Tue, 17 Mar 2015 14:25:27 GMT

Hi,

I cannot attach the SAS file. So I have attached the CSV file.

Re: How to remove digits from the observations?

Tom — Tue, 17 Mar 2015 14:29:47 GMT

You did not include the key information from the log that would show what statement was on line 127 and what variable was being referenced at columns 9 and 16.

Are you trying to remove digits from the middle of a NUMBER? If so then those are not errors just notes showing that SAS had to convert the number to a character string to perform the substr() and then convert the string result back to a number.

Re: How to remove digits from the observations?

AbuChowdhury — Tue, 17 Mar 2015 14:32:30 GMT

No, I want to remove the first two digits and last two digits from "cusip" observations. Those notes were shown in log file. And the output dataset shows blank observations i.e. no observation at all.

Re: How to remove digits from the observations?

art297 — Tue, 17 Mar 2015 14:52:05 GMT

I will guess that your error occurred when you imported the file and possibly read the first line (the variable name header) as data. The following worked for me:

data have;

format tradedate ddmmyy10.;

infile "c:\temp\buy_16mar2015.csv" dlm=',' firstobs=2;

input tradedate ddmmyy10. cusip :$15.;

run;

data want;

set have;

cusip = substr(cusip, 3, length(cusip)-4);

run;

Re: How to remove digits from the observations?

naveen_srini — Tue, 17 Mar 2015 15:41:27 GMT

Thanks Abu for making it more clear. Thumb rule is always to explain the question well by helping us to help you. I have made the necessary changes in the following:

data want;

set have;

do i= 1 to length(cusip);

new_cusip=substr(cusip,i,1);

if i in (1,2) or i=length(cusip)-1 or i=length(cusip) then continue;

group=_n_;

output;

end;

run;

data final;

array a(15) $1 _temporary_;

call missing (of a{*});

do _n_=1 by 1 until(last.group);

set want;

by group;

a(_n_)=new_cusip;

trunc_cusip=cats(of a{*});

end;

keep tradedate trunc_cusip;

format tradedate ddmmyy10.;

run;

Have a nice day,

Naveen Srinivasan

L&T Infotech

Re: How to remove digits from the observations?

MarkWik — Tue, 17 Mar 2015 19:33:11 GMT

Hello, It would be nice and courteous of you if you could mark the question as answered and gift the points to the people who have helped you. Asking questions, one after another is alright but please acknowledge the help. Thanks

Re: How to remove digits from the observations?

AbuChowdhury — Wed, 18 Mar 2015 21:50:39 GMT

Hi,

I will definitely do that. I was just checking all codes. And thanks a lot to everybody. I am grateful to all of you. I wanted to mark 'correct' or 'helpful' to everybody but I cannot.

Re: How to remove digits from the observations?

art297 — Wed, 18 Mar 2015 22:09:07 GMT

Abu: Out of curiosity, why didn't Astounding's originally suggested code work for you?

Re: How to remove digits from the observations?

AbuChowdhury — Wed, 18 Mar 2015 23:43:38 GMT

Hi Arthur,

I don't know actually. It showed the following notes and output dataset had no observations.

NOTE: Numeric values have been converted to character values at the places given by:

(Line):(Column).

127:16

NOTE: Character values have been converted to numeric values at the places given by:

(Line):(Column).

127:9

It may be because I missed something while running the code. I also wanted to mark Astounding's code as correct or helpful answer, but I couldn't. After I marked two helpful answers, I found that I cannot choose anymore. But Astounding's code is really helpful (and may be correct; I don't know exactly since I could not run the code).