topic Re: Change 1Â 056 ---> 1056 in SAS Programming

Change 1Â 056 ---> 1056

WilliamB — Thu, 27 Oct 2016 13:34:34 GMT

Hello

I have a excel table and for the thousands I have a "Â" and therefore I would like to remove the "Â" to keep numeric variables without changing the .xlsx file.
My goal transform this: 1Â 056

988 --> 988
1Â 056 ---> 1056

Sometimes, there is no "Â":

17923	232	641
17924	240	735
17925	323	1Â 208
17926	233	769
17927	194	634
17928	150	428
17929	146	394
17930	278	791
17931	224	637
17932	275	725
17933	276	1Â 101
17934	247	1Â 103
17935	154	412
17936	192	609
17937	308	992
17938	333	1Â 287
17939	342	1Â 250
17940	375	1Â 975
17941	275	1Â 019

.....

I should probably create a informat?

Finally, the last line is the total of columns 2.3.

Here is the false start code.

data toto;
infile 'C:\.........\SPV11.xlsx' dlm=',' firstobs=8 truncover;
input x1 :ddmmyy. x2 :numx. x3 :numx.;
run;

proc print data=toto(obs=350);run;

Attachements: my .xlsx file.

Is there any simple solution to those operations? Thank you!

Re: Change 1Â 056 ---> 1056

RW9 — Thu, 27 Oct 2016 13:55:15 GMT

Personally I would fix this in the XLS file - why can you not fix the file you have, if its wrong then not chaning it there will cause more issues further down the line - always try to fix things "at source". If that is not possible then copy the file, find and replace in the new file, and use that - oh, make sure you do a QC on that. You can also do it in SAS - assuming that SAS actually reads in that character - you can use the tranwrd() function to perform a find and replace. I can;t provide any exact code as I don;t download Excel files, but it is very simple, just follow the guidance on the SAS pages:

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000215027.htm

Re: Change 1Â 056 ---> 1056

Astounding — Thu, 27 Oct 2016 14:07:00 GMT

If the data can't be changed in Excel, you will need to read those fields into SAS as character. Having done that, you could try:

field = compress(field, , 'kd');

That will keep only the digits. So it will get rid of anything else such as decimal points, negative signs ... anything that is not a digit. If that is not sufficient for your needs, take a look at some of the additional options that are permissible within the third parameter of the COMPRESS function. It can be done, but first you have to decide which characters (in addition to digits) you wish to keep.

Re: Change 1Â 056 ---> 1056

Kurt_Bremser — Thu, 27 Oct 2016 14:34:09 GMT

If that code:

data toto;
infile 'C:\.........\SPV11.xlsx' dlm=',' firstobs=8 truncover;
input x1 :ddmmyy. x2 :numx.  x3 :numx.;
run;

actually worked, your file is not an Excel file at all, since infile and input only work on text files. XLSX files are zip-compressed XML and can't be read into SAS like this.