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.
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;
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;
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;
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.
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.
Ready to level-up your skills? Choose your own adventure.