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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.