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-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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