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
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
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
Thanks Gaurang and Ksharp,
I really appreciate this help.
Mark
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.