My pleasure @psnorrod  ... View more

You're welcome  ... View more

Hi @Vijay77    Could you please clarify your request by providing sample data and desired output? Thank you!  ... View more

You're welcome! Could you please mark the topic as answered? Thank you 🙂 ... View more

Hi @NOMBUH    You can do that in a data step using an array: proc sql noprint; select max(total) into: max_obs from (select count(*) as total from table1 group by Name); quit; data table2; set table1; by Name; array Code_(&max_obs) $ /* Add $ if Code is a character variable */; array Amount_(&max_obs) $ /* Add $ if Code is a character variable */; if first.Name then do; counter=0; call missing(of Code_(*)); call missing(of Amount_(*)); end; counter+1; retain Code_; retain Amount_; do i=1 to &max_obs; Code_(counter)=Code; Amount_(counter)=Amount; end; if last.Name then output; drop Code Amount counter i; run; You can also use a proc transpose, as suggested by @Kurt_Bremser :   proc transpose data=table1 out=table1_tr_code (drop=_name_) prefix=Code_; var Code; by Name; run; proc transpose data=table1 out=table1_tr_amount (drop=_name_) prefix=Amount_; var Amount; by Name; run; data table2; merge table1_tr_code table1_tr_amount; by Name; run;   ... View more

Hi @kmardinian    You can do this: data have; input subject site; cards; 10021 644 10021 644 10256 644 10256 644 56985 733 56985 733 45698 733 12659 733 ; run; proc sql; create table want as select site, count(distinct subject) as count from have group by site; run; ... View more

Hi @radhikaa4    The solution you described is the easiest.   You can also define your own function using proc fmcp. It is easier to maintain if the rule is going to change in the future and if you use it in multiple datasets. /* Function definition -> Fcalc_Day() with 2 arguments as defined below */ libname lib_fcmp "&path."; /* to be specified */ proc fcmp outlib=lib_fcmp.functions.dev; function Fcalc_Day(randdate, startdate); if randdate > startdate then return (startdate - randdate); if randdate <= startdate then return (startdate - randdate + 1); endsub; quit; options cmplib=lib_fcmp.functions; /* Use case */ data have; input subjectid startdate : mmddyy10. randdate : mmddyy10.; format startdate randdate mmddyy10.; cards; 1 12/31/2018 1/1/2019 1 1/1/2019 1/1/2019 1 1/2/2019 1/1/2019 1 1/3/2019 1/1/2019 1 1/4/2019 1/1/2019 ; run; data want; set have; day = Fcalc_Day(randdate,startdate); run; ... View more

Hi @greg6363    Do you expect something like this ?   proc sql noprint; select max(total) into: max_obs from (select count(*) as total from have group by ID1, ID2); quit; data want; set have; by ID1 ID2; array ID3_(&max_obs) $; array Date1_(&max_obs); array Amount_(&max_obs); array Date2_(&max_obs); array Code1_(&max_obs) $; array Date3_(&max_obs); array Action1_(&max_obs) $; array Indicator1_(&max_obs); array Indicator2_(&max_obs); if first.ID2 then do; counter=0; call missing(of ID3_(*)); call missing(of Date1_(*)); call missing(of Amount_(*)); call missing(of Date2_(*)); call missing(of Code1_(*)); call missing(of Date3_(*)); call missing(of Action1_(*)); call missing(of Indicator1_(*)); call missing(of Indicator2_(*)); end; counter+1; retain ID3_; retain Date1_; retain Amount_; retain Date2_; retain Code1_; retain Date3_; retain Action1_; retain Indicator1_; retain Indicator2_; do i=1 to dim(ID3_); ID3_(counter)=ID3; Date1_(counter)=Date1; Amount_(counter)=Amount; Date2_(counter)=Date2; Code1_(counter)=Code1; Date3_(counter)=Date3; Action1_(counter)=Action1; Indicator1_(counter)=Indicator1; Indicator2_(counter)=Indicator2; end; format Date: MMDDYY8.; if last.ID2 then output; drop ID3 Date1 Amount Date2 Code1 Date3 Action1 Indicator1 Indicator2 counter i; run; ... View more