SAS EXCEL retrieving data

[BartDekeyser]

Hi all ,

i have the following Excel  

0116

01161

01161A

01161A1

01161A2

01161B

01161B1

and so on. How can i manipulate my data to only keep the lowest sublevel meaning for this case : 

01161A1

01161A2

01161B1

The file contains 1000+ .

[Kurt_Bremser]

I have no idea how you do that in Excel, but in SAS you can use this SQL step:

proc sql;
create table want as
  select *
  from have
  having length(string) = max(length(string))
;
quit;

[BartDekeyser]

hmm this will only work if all sublevel have the same length but they dont.

Ill give a more detailed example : 

0116

01161

01161A

01161A1

01161A2

01161B

01161B1

01161C

01161D

01162

01163

01163A

and so on. With you solutions i would only keep : 01161A1, 01161A2,01161B1. But i actually i would like to have those but also : 01161C, 01161D, 01162, 01163A

(for sas your sql could be copied right away ^^

[ballardw]

---

@BartDekeyser wrote:

hmm this will only work if all sublevel have the same length but they dont.

Ill give a more detailed example : 

0116

01161

01161A

01161A1

01161A2

01161B

01161B1

01161C

01161D

01162

01163

01163A

and so on. With you solutions i would only keep : 01161A1, 01161A2,01161B1. But i actually i would like to have those but also : 01161C, 01161D, 01162, 01163A

(for sas your sql could be copied right away ^^

---

My first thought with the original post was "This is not a clearly defined "lowest level"." And your expanded reference data confirms that. Which indicates to me that you have multiple values in a single variable and may perhaps need to parse out the base value, first sublevel, second sublevel (and as many identifiable sublevels as you may have into separate variables (temporarily) to process and then select.