topic Re: trailing spaces when exporting data from excel to sas in SAS Procedures

trailing spaces when exporting data from excel to sas

msfelicity84 — Thu, 26 May 2016 14:31:47 GMT

I am exporting data from excel to sas. After formating cells to text how can I use "find and replace" to remove the trailing spaces

example below:

If IDORNUM='

1217063

'then

S_ANG=

;

If CIDORNUM=' 0857794 'then S_ANG= 45 ;

Re: trailing spaces when exporting data from excel to sas

ballardw — Thu, 26 May 2016 14:43:46 GMT

It is not clear from your question whether you are attempting to manipulated data in SAS (SAS does not use CELLS generally, variables are the base of most manipulations).

Also your example code doesn't quite "remove spaces" it is assigning a value. If you you mean how can I assign a numeric value based on the value of a character then the code you post will do that as SAS generally ignores trailing spaces in comparisons.

You may need to provide more complete deatils of your data and what you are attempting to accomplish.

Re: trailing spaces when exporting data from excel to sas

msfelicity84 — Thu, 26 May 2016 15:12:39 GMT

Thank you Ballardw, I'm just trying to remove the space after copying my data from the cells. (I have about 500 rows of data)

Excel example below:

Ifnum='

0857794

'then

S_ANG=

Below is how the data looks when I pastes into thesasWindow

If NUM=' 0857794 'then S_ANG= 45 ;

Im just trying to remove the extra space in between the quotes

Thanks again for your assistance.

Re: trailing spaces when exporting data from excel to sas

ballardw — Thu, 26 May 2016 17:13:22 GMT

If you want to change the value of the variable Num from ' 0857794 ' to '0857794' (SAS will ignore the trailing spaces) easiest might be

Num= strip(num);

If you have bunch of variables to do this with then an array:

data want;

set have;

array v Num Idnum ....; /* list the variables to clean up. If it is all of your character values you can use the special list word _character_

to indicate all of them*/

do i = 1 to dim(v);

v[i] = strip(v[i]);

end;

drop i;

run;

Re: trailing spaces when exporting data from excel to sas

msfelicity84 — Thu, 26 May 2016 17:57:30 GMT

Great I will try this now, thank you very much!!!

Re: trailing spaces when exporting data from excel to sas

FreelanceReinh — Thu, 26 May 2016 20:57:01 GMT

@msfelicity84: If it works, I must have misunderstood what you were doing.

To me it looks like you have SAS code snippets in an Excel table (maybe you created repetitive code using Excel techniques). Then you talked about copying and pasting into the SAS window, i.e., you pasted code from Excel to the Enhanced Editor via the Windows clipboard. Now you want to tidy up the pasted code by removing/inserting "blanks" where necessary (and think about "find and replace" -- which could indeed be the right tool to achieve this).

I suspect that some of the "blanks" might in fact be tab characters (the tabs which separated adjacent Excel cells), but this might depend on Enhanced Editor settings and could be handled with "find and replace" as well.

Obviously, this way of creating SAS code should not be part of the regular development process, but might be acceptable under certain circumstances for a one-time task if the SAS code will be validated properly anyway later on.