BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jimmychoi
Obsidian | Level 7

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_NameRDMFGFailed
1abtik agRD.PRO F.PRO
21st Century Oncology Inc M.PROF.PRO
21st Century TherapeuticsRD.PRO  
22nd Century Group IncRD.PROM.PRO 

 

below is the desired output:

Company_NameRDMFGFailed
1abtik agRD.PRO  
1abtik ag  F.PRO
21st Century Oncology Inc M.PRO 
21st Century Oncology Inc  F.PRO
21st Century TherapeuticsRD.PRO  
22nd Century Group IncRD.PRO  
22nd Century Group Inc M.PRO 
1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

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;

View solution in original post

3 REPLIES 3
novinosrin
Tourmaline | Level 20

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;
hashman
Ammonite | Level 13

@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.

Ksharp
Super User
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;

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 552 views
  • 3 likes
  • 4 in conversation