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