Hello, For each customer ID there is a column that contain reasons for overide. This field called X (reasons for overide) is concatenation of strings. My question: What is the way to create the want data set? The want data set will contain multiple new columns (Each overide reason will have column ) and will get binary values 1/0
data have;
input ID X $20.;
cards;
111 RF201|RF205|RF209
222 RF201|RF211
333 RF304
;
Run;
data want;
input ID RF201 RF205 RF209 RF211 RF304 ;
datalines;
111 1 1 1 0 0
222 1 0 0 1 0
333 0 0 0 0 1
;
run;
data have;
input ID X $20.;
cards;
111 RF201|RF205|RF209
222 RF201|RF211
333 RF304
;
Run;
data temp;
set have;
value=1;
do i=1 to countw(X,'|');
temp=scan(X,i,'|');output;
end;
keep id temp value;
run;
proc transpose data=temp out=temp2(drop=_name_);
by id;
var value;
id temp;
run;
proc stdize data=temp2 out=want reponly missing=0;
run;
data have;
input ID X $20.;
cards;
111 RF201|RF205|RF209
222 RF201|RF211
333 RF304
;
Run;
data temp;
set have;
value=1;
do i=1 to countw(X,'|');
temp=scan(X,i,'|');output;
end;
keep id temp value;
run;
proc transpose data=temp out=temp2(drop=_name_);
by id;
var value;
id temp;
run;
proc stdize data=temp2 out=want reponly missing=0;
run;
Thanks,
When I add one more row with missing value (X column) then I recieve a warning
WARNING: 1 observations omitted due to missing ID values.
What is the way to prevent this warning?
Data have;
input CustID X $20.;
cards;
111 RF201|RF205|RF209
222 RF201|RF211
333 RF304
444
;
Run;
/****Wide To Long****/
data temp;
set have;
value=1;
do i=1 to countw(X,'|');
temp=scan(X,i,'|');
output;
end;
keep CustID temp value;
run;
/****Long To Wide****/
proc transpose data=temp out=temp2(drop=_name_);
by CustID;
var value;
id temp;
run;
/*WARNING: 1 observations omitted due to missing ID values.*/
/***Convert Missing into zero***/
proc stdize data=temp2 out=want reponly missing=0;
run;
another way:
data split;
set have;
length rf $ 5;
do i = 1 to countw(x, '|');
rf = scan(x, i, '|');
output;
end;
drop x i;
run;
proc sql noprint;
select distinct rf
into :rf_list separated by ' '
from work.split;
quit;
data want;
set split;
by id;
length &rf_list. 8;
retain &rf_list.;
array rf_list &rf_list.;
if first.id then do;
do i = 1 to dim(rf_list);
rf_list[i] = 0;
end;
end;
rf_list[findw("&rf_list", rf, ' ', 'ets')] = 1;
if last.id then output;
drop i rf;
run;
If you know in advance the names you want to use for the flag variables (and the names matches the text you have in your X variable) you can just use an ARRAY and the FINDW() function.
data have;
input ID X $20.;
cards;
111 RF201|RF205|RF209
222 RF201|RF211
333 RF304
444
;
data want;
set have;
array flags RF201 RF205 RF209 RF211 RF304;
do over flags;
flags=0<findw(x,vname(flags),'|','ti');
end;
run;
/*
OK.It looks like you have some unexpected data.
*/
Data have;
input CustID X $20.;
cards;
111 RF201|RF205|RF209
222 RF201|RF211
333 RF304
444
;
Run;
/****Wide To Long****/
data temp;
set have(where=(X is not missing));
value=1;
do i=1 to countw(X,'|');
temp=scan(X,i,'|');
output;
end;
keep CustID temp value;
run;
/****Long To Wide****/
proc transpose data=temp out=temp2(drop=_name_) ;
by CustID;
var value;
id temp;
run;
/*Combine ID with missing X back.*/
data temp2;
set temp2 have(where=(X is missing));
drop X;
run;
/***Convert Missing into zero***/
proc stdize data=temp2 out=want reponly missing=0;
run;
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
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.