BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
NagendraBS
Fluorite | Level 6

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
arodriguez
Lapis Lazuli | Level 10

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

3 REPLIES 3
arodriguez
Lapis Lazuli | Level 10

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;

 

 

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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