BookmarkSubscribeRSS Feed
WilliamB
Obsidian | Level 7

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: 056                           

988       -->  988
056 ---> 1056

 Sometimes, there is no "Â":

 

 

17923232641
17924240735
179253231 208
17926233769
17927194634
17928150428
17929146394
17930278791
17931224637
17932275725
179332761 101
179342471 103
17935154412
17936192609
17937308992
179383331 287
179393421 250
179403751 975
179412751 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!

3 REPLIES 3
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

Astounding
PROC Star

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.

Kurt_Bremser
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 801 views
  • 0 likes
  • 4 in conversation