DATA Step, Macro, Functions and more

How to define Flag to the customer who has more than one account of different types.

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

How to define Flag to the customer who has more than one account of different types.

Hi Friends,

 

Please find a soluiton for this,

 

I have a dataset with one customer has more than one acount of different types and i need to flag to customer who has web account.

 

example.

 

cust acct type flag

1     201  sav   N

1     202  web  Y

1     203  tda   N

1    204   deb  N

2    301   web Y

2    302   DEB N

2    303   web  Y

2    304   sav  N

 

Result:

Cust  acct1 acct2 acct3 acct4 flag

1       sav     web   tda    deb    Y

2       Web    Deb   web  sav    Y

 

Thanks in advance,

 

Regards,

Nagendra. 


Accepted Solutions
Solution
‎09-25-2015 08:58 AM
Frequent Contributor
Posts: 144

Re: How to define Flag to the customer who has more than one account of different types.

[ Edited ]

Hi,

You could sort by  Cust and Flag and keep last record(Y will be after N).

 

Proc sort data=have;
  by cust flag;
run;
 
data aux_want;
  set have;
   by cust flag;
  if last.flag;/* If customer have any Y will be the last, if not, flag=N*/
  keep cust flag
run;

 

 

This will made that you have the same flag for each cust. Then  you could transpose your dataset.

 

data have;
  set have;
  var='acct'||substr(acct,length(acct),1); /*Name for columns*/
run;
 
proc transpose data=have out=want;
  by cust;
  id var;
  var type;
run;
 
data want;
  merge want
        aux_want;
  by cust;
run;

 

 

 

View solution in original post


All Replies
Solution
‎09-25-2015 08:58 AM
Frequent Contributor
Posts: 144

Re: How to define Flag to the customer who has more than one account of different types.

[ Edited ]

Hi,

You could sort by  Cust and Flag and keep last record(Y will be after N).

 

Proc sort data=have;
  by cust flag;
run;
 
data aux_want;
  set have;
   by cust flag;
  if last.flag;/* If customer have any Y will be the last, if not, flag=N*/
  keep cust flag
run;

 

 

This will made that you have the same flag for each cust. Then  you could transpose your dataset.

 

data have;
  set have;
  var='acct'||substr(acct,length(acct),1); /*Name for columns*/
run;
 
proc transpose data=have out=want;
  by cust;
  id var;
  var type;
run;
 
data want;
  merge want
        aux_want;
  by cust;
run;

 

 

 

Occasional Contributor
Posts: 13

Re: How to define Flag to the customer who has more than one account of different types.

Thanks a lot...
Super User
Super User
Posts: 7,413

Re: How to define Flag to the customer who has more than one account of different types.

Well, I can't see why that structure would be useful, however this code will achieve it:

data have;
  input cust acct type $ flag $;
datalines;
1 201 sav N
1 202 web Y
1 203 tda N
1 204 deb N
2 301 web Y
2 302 DEB N
2 303 web Y
2 304 sav N
;
run;

data want (drop=tmp type i);
  set have (rename=(acct=tmp));
  by cust;
  array acct{4} $3.;
  retain acct1-acct4 has_web i;
  if first.cust then i=1;
  acct{i}=type;
  if type="web" then has_web="Y";
  i=i+1;
  if last.cust then output;
run;

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 231 views
  • 3 likes
  • 3 in conversation