topic Re: Making a duplicate row or "splitting a row in to many" in SAS Programming

Making a duplicate row or "splitting a row in to many"

jimmychoi — Tue, 21 Jan 2020 13:49:13 GMT

Dear experts,

I want to duplicate a data set.

and what I want is to duplicate or split a specific row having more than one "RD", "MFG", "Failed" marked.

Company_Name	RD	MFG	Failed
1abtik ag	RD.PRO		F.PRO
21st Century Oncology Inc		M.PRO	F.PRO
21st Century Therapeutics	RD.PRO
22nd Century Group Inc	RD.PRO	M.PRO

below is the desired output:

Company_Name	RD	MFG	Failed
1abtik ag	RD.PRO
1abtik ag			F.PRO
21st Century Oncology Inc		M.PRO
21st Century Oncology Inc			F.PRO
21st Century Therapeutics	RD.PRO
22nd Century Group Inc	RD.PRO
22nd Century Group Inc		M.PRO

Re: Making a duplicate row or "splitting a row in to many"

novinosrin — Tue, 21 Jan 2020 14:05:34 GMT

Hi @jimmychoi Long time,, hope you are well


data have;
input Company_Name & $30.	(RD	MFG	Failed) ($);
cards;
1abtik ag	RD.PRO	.　	F.PRO
21st Century Oncology Inc	.　	M.PRO	F.PRO
21st Century Therapeutics	RD.PRO	　. .	　
22nd Century Group Inc	 RD.PRO	M.PRO	.　
;

data want;
 set have;
 array t rd mfg failed;
 array temp(3) $20;
 if dim(t)-cmiss(of t(*))>1 then do;
 do over t;
  temp(_i_)=t;
 end;
 call missing(of t(*));
 do over t;
  if temp(_i_)>' ' then do;
  t=temp(_i_);
  output;
  call missing(t);
  end;
 end;
 end;
 else output;
 drop temp:;
run;

Re: Making a duplicate row or "splitting a row in to many"

hashman — Tue, 21 Jan 2020 20:54:38 GMT

@jimmychoi:

The concept is the same as @novinosrin's, just the mechanism of resurrecting the variables after they are nulled is a bit different:

data have ;                                                                                                                                      
  input (company_name rd mfg failed) ($) ;                                                                                                       
  cards ;                                                                                                                                        
1abtik RD.PRO   .     F.PRO                                                                                                                      
Oncol    .     M.PRO  F.PRO                                                                                                                      
Thera  RD.PRO   .      .                                                                                                                         
Group  RD.PRO  M.PRO   .                                                                                                                         
;                                                                                                                                                
                                                                                                                                                 
data want (drop = _:) ;                                                                                                                          
  set have ;                                                                                                                                     
  array cc rd mfg failed ;                                                                                                                       
  do over cc ;                                                                                                                                   
    _t = cc ;                                                                                                                                    
    call missing (of cc[*]) ;                                                                                                                    
    cc = _t ;                                                                                                                                    
    if not cmiss (cc) then output ;                                                                                                              
    set have point = _n_ ;                                                                                                                       
  end ;                                                                                                                                          
run ;

Kind regards

Paul D.

Re: Making a duplicate row or "splitting a row in to many"

Ksharp — Wed, 22 Jan 2020 12:44:29 GMT

data have;
infile cards expandtabs truncover;
input Company_Name & $30.	(RD	MFG	Failed) ($);
cards;
1abtik ag	  RD.PRO . 	F.PRO
21st Century Oncology Inc	  . M.PRO	F.PRO
21st Century Therapeutics	  RD.PRO	 .  .	　
22nd Century Group Inc	   RD.PRO	M.PRO  . 
;
proc transpose data=have out=temp;
by Company_Name;
var RD	MFG	Failed;
run;

data temp;
 set temp(where=(col1 is not missing));
 by Company_Name;
 if first.Company_Name then n=0;
 n+1;
run;
proc transpose data=temp out=want(drop=_:);
by Company_Name n;
var col1;
id _name_;
run;