topic Re: Split column into multiple columns in SAS Programming

Split column into multiple columns

Ronein — Mon, 31 Jul 2023 07:24:26 GMT

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;

Re: Split column into multiple columns

Ksharp — Mon, 31 Jul 2023 09:43:05 GMT

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;

Re: Split column into multiple columns

andreas_lds — Mon, 31 Jul 2023 09:53:29 GMT

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;

Re: Split column into multiple columns

Ronein — Mon, 31 Jul 2023 10:29:32 GMT

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;

Re: Split column into multiple columns

Tom — Mon, 31 Jul 2023 11:58:03 GMT

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;

Re: Split column into multiple columns

Ksharp — Tue, 01 Aug 2023 11:45:03 GMT

/*
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;