topic Re: SAS EXCEL retrieving data in SAS Programming

SAS EXCEL retrieving data

BartDekeyser — Wed, 15 Jul 2020 14:14:06 GMT

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+ .

Re: SAS EXCEL retrieving data

Kurt_Bremser — Wed, 15 Jul 2020 14:19:38 GMT

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;

Re: SAS EXCEL retrieving data

BartDekeyser — Wed, 15 Jul 2020 14:25:57 GMT

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 ^^

Re: SAS EXCEL retrieving data

ballardw — Wed, 15 Jul 2020 15:18:52 GMT

@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.