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

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

NagendraBS — Fri, 25 Sep 2015 06:03:49 GMT

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.

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

arodriguez — Fri, 25 Sep 2015 07:07:01 GMT

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;

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

RW9 — Fri, 25 Sep 2015 09:15:38 GMT

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;

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

NagendraBS — Fri, 25 Sep 2015 12:58:51 GMT

Thanks a lot...