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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.