Import with substring

[Bellefeuille]

Hello this might be a two part question  and i appologize if i dont get the wording correctly,

I have an excel file that has spaces in the name.
Example Client Key

what im trying to do is, when i try to import the excel it does not like it when i try to change the naming convention to sas friendly format?  lets me know that some names migt be too big.

my main goal is to replicate the imported file but with a need to create an alias column from the client key because what i need is in the middle of each varaible.

Client key data format is similar to 004111111111134, so i need to find a way to only extract the 1's for each record. using instring? or mid?

is there an easy way to create this table in EG?

Thanks!

[Reeza]

What happens if you have two sequences of 1s

01111222221111110

[Bellefeuille]

Each Client key is unique(primary) the 1's represent a client ID, which is embeded in the client key.

So im mainly trying to extract the unique Client id from the key.

[ballardw]

Since I doubt that your "unique identifier" consists of 1s do you mean that you are trying to extract the characters starting at position 4 and ending at position 13?

If your variable is character that would be

ID = substr(Client_key,4,10);  /* SAS does not have a MID function, substr does similar when a second position is used*/

If your column headings are longer than 32 characters SAS will apply rules to create SAS useable names from whatever appears in the heading when you use proc import. Anything longer than 32 characters gets truncated, generally spaces and non-alphanumeric characters will be changed to underscores. If the first character is a digit it will likely be replaced with an _, if two columns would have the same name then the variable will be suffixed at some point with a numeric value to indicate a different column.

If you don't like automatically generated names then place column headers in the Excel that are useable as SAS variable names OR use a different approach to bringing data into SAS such as save to CSV and use a data step to read values into variable names you assign.