topic Re: How to Create a Flagging Column Based on The Maximum Value Of a Feature ID ? in SAS Programming

How to Create a Flagging Column Based on The Maximum Value Of a Feature ID ?

Midi — Thu, 25 Jul 2019 10:31:26 GMT

Hello Everyone.

I have This DataSet:

data test;
input Feature_ID Client_ID;
cards;
52004 541111
56222 541111
56300 541111
73222 980002 
73600 980002
78006 980002
85000 980002
95001 1000001
98020 1000001
;
run;

And I want to Create a Flag Column , That Takes 1 for the Maximum Feature_ID for Each Client .

The Result Should be as follow:

data test;
input Feature_ID Client_ID Flag;
cards;
52004 541111 0
56222 541111 0
56300 541111 1
73222 980002 0
73600 980002 0
78006 980002 0
85000 980002 1
95001 1000001 0
98020 1000001 1
;
run;

How Can I do it ?

What I've done (Because the Original Data was not ordered) , i started by ordering the Data , using Proc Sql , This Way:

proc sql;
   create table tab_Trial as select
               Feature_ID
              ,Client_ID
           from Test
           order by Feature_ID, Client_ID;
quit;

And Then Tried this Code , to Create the Flag Column :

data Flagging; 
set Tab_Trial; 
Flag=ifn(max(Feature_ID),1,0); 
by Client_ID;
run;

But it didn't worked , it marked every line with 1 , my guess is that , i have to use First.Feature_ID but alongside the Max() function is what got me confused , any help would be much appreciated.

Thank you

Re: How to Create a Flagging Column Based on The Maximum Value Of a Feature ID ?

KachiM — Thu, 25 Jul 2019 11:29:32 GMT

Try this:

data Flagging;
   do until(last.Client_ID);
      set Tab_Trial;
      by Client_ID;
      Flag = ifN(last.Client_ID, 1, 0);
      output;
   end;
run;

Re: How to Create a Flagging Column Based on The Maximum Value Of a Feature ID ?

novinosrin — Thu, 25 Jul 2019 11:41:33 GMT

Hello @Midi



data test;
input Feature_ID Client_ID;
cards;
52004 541111
56222 541111
56300 541111
73222 980002 
73600 980002
78006 980002
85000 980002
95001 1000001
98020 1000001
;
run;
/*Using Proc SQL*/
proc sql;
create table want as
select *,max(feature_id)=feature_id as Flag
from test
group by client_id
order by client_id ,feature_id;
quit;

/*Data step if your dataset is sorted by client_id feature_id */

data want;
set test;
by client_id;
Flag=last.client_id=1;
run;

Re: How to Create a Flagging Column Based on The Maximum Value Of a Feature ID ?

Midi — Thu, 25 Jul 2019 12:49:27 GMT

Thank u , very much , i got where i messed up.

Thank you again.

Re: How to Create a Flagging Column Based on The Maximum Value Of a Feature ID ?

Midi — Thu, 25 Jul 2019 12:50:15 GMT

Thank you a lot.