Help required in Conditional function for new variable extraction?

[MarkWik]

Hey Everyone,

Help required in Conditional function for new variable extraction?

I have an Input Table_A with one variable as follows :

NAME_CONVENE

1 City

2 Village

3 Countryside

1 width <35

2 width <98

3 width 130-145

4 width 145+

Y1 Young & homeless

F1 Riche-super Elite

F2 Singapore

A3 Malaysia Government Council Magistrate Municipality

I need my Output as follows in Table_B:

NAME_CONVENE/*Input-have*/                  NAME_CONVENE_INDEX/*Output-want*/        NAME/*Output-want*/

1 City                                                                       1                                                     City

2 Village                                                                   2                                                     Village

3 Countryside                                                            3                                                    Countryside

1 width <35                                                               1                                                      <35

2 width <98                                                               2                                                      <98

3 width 130-145                                                         3                                                      130-145

4 width 145+                                                             4                                                      145+

Y1 Young & homeless                                               1                                                      Young & homeless

F1 Riche-super Elite                                                  1                                                      Riche-super Elite

F2 Singapore                                                            2                                                       Singapore

A1 Malaysia                                                             1                                                        Malaysia

A2 Malaysia                                                             2                                                       Malaysia

A3 malaysia                                                             3                                                        Malaysia

Government                                                              1                                                        Government

Council                                                                     2                                                       Council

Magistrate                                                                3                                                        Magistrate

Municipality                                                              4                                                       Municipality

In the above, you would notice that Government, Council, Magistrate and Municipality has no numerals attached and for that NAME_CONVENE_INDEX has to be assigned sequentially as it is in the sample above. Your help in logic would mean a lot. Thank you.

Mark

[Ksharp]

data have;

input NAME_CONVENE & $20.;

cards;

1 City

2 Village

3 Countryside

1 width <35

2 width <98

3 width 130-145

4 width 145+

Y1 Young & homeless

F1 Riche-super Elite

F2 Singapore

A3 Malaysia

Government

Council

Magistrate

Municipality

;

run;

data want;

set have;

if anydigit(NAME_CONVENE) then do;

   NAME_CONVENE_INDEX=compress(substr(NAME_CONVENE,1,findc(NAME_CONVENE,' ')) , ,'kd');

   NAME=substr(NAME_CONVENE,findc(NAME_CONVENE,' '));

   if anydigit(NAME) then NAME=compress(NAME, ,'a');

end;

else NAME=NAME_CONVENE;

if missing(NAME_CONVENE_INDEX) then do;n+1;NAME_CONVENE_INDEX=n;end;

run;

Ksharp

Message was edited by: xia keshan

[Gaurang_sas]

Hi MarkWik,

The following code will resolve your problem.

data table_b;

set table_a;

NAME_CONVENE_INDEX= substr(NAME_CONVENE,anydigit(NAME_CONVENE,1),1);

NAME= trim(left(substr(NAME_CONVENE,anydigit(NAME_CONVENE,1)+1,length(NAME_CONVENE)-anydigit(NAME_CONVENE,1))));

run;

Please give your feedback.

Thank you.

Gaurang Patel

[MarkWik]

Thanks Gaurang and Ksharp,

I really appreciate this help.

Mark