Hey so the dataset anova_analysis2023 is not included in the post. Hope this link helps https://blogs.sas.com/content/iml/2018/12/19/visualize-mixed-model.html   ... View more

I don’t believe so. ‘if yearc ne “unkk” then do;’ statement removes any year with missing from below logic ... View more

data vy1;   set vy; /*Seperate date into day, month, year */   dayc=substr(date,1,2);   monthc=substr(date,3,3);   yearc=substr(date,6,4);     if yearc ne "unkk" then do; /*One row has year missing, not able to impute */   /*both month and day missing*/   if dayc="uk" and monthc="unk" then do;      dayi="31";      monthi="DEC";   end;     /*only day missing*/   if dayc="uk" and monthc ne"unk" then do;      myrc=cats(monthc)||cats(yearc);      myn=input(myrc, anydtdte7.);      lastday=intnx('month',myn,0,'E');   end;      /*get imputed date*/   if dayi ne "" and monthi ne "" then   date_impc=cats(dayi)||cats(monthi)||cats(yearc);   if lastday ne . then date_impc=put(lastday, date9.);     format date_imp date9.;   date_imp=input(date_impc, date9.);   end; run; ... View more

proc sgplot data=example noautolegend; by paramn param avision trta usubjid; scatter x=parcat3n y=value / group=trta groupdisplay=cluster clusterwidth=0.5 markerattrs=( size=10)errorbarattrs=(thickness=2); styleattrs datasymbols=(circle); yaxis label='Aval (D)' grid values=(-7.25 to 7.25 by .25); xaxis offsetmax=0.08 fitpolicy=rotate values=(-0.41 -0.2 0 0.2 0.41 0.61 0.81 1.02 1.22 1.43 1.63 1.83 2.04 2.24 2.44 2.65 2.85 3.06 3.26); keylegend / title="Treatment" ; run;   So this is a scatterplot of x=parcat3n y=value. Are you trying to plot a scatterplot? I think you want to plot the line-graph. I'm assuming here that you've used a regression model? You've output a dataset with model estimates so you should be able to plot the regression directly   proc sgplot data=sashelp.class noautolegend; title "Regression Line with Slope and Intercept"; reg y=weight x=height; run; If it's not a regression, then what model are you using?  ... View more

data have; input ID $ DT:date9. Amount Consecutive_Amount; format DT date9.; datalines; A 09JUL2021 3600 3600 A 03AUG2021 456 489 A 04AUG2021 33 33 A 06AUG2021 235 335 A 07AUG2021 100 100 A 09AUG2021 86 86 A 12AUG2021 456 456 A 24AUG2021 22 1925 A 25AUG2021 987 1984 A 26AUG2021 916 997 A 27AUG2021 81 81 B 07AUG2021 554 992 B 08AUG2021 193 438 B 09AUG2021 245 245 ; run;   /*using lag function to check consecutive days*/ data have1;   set have;   by id;   format lagdt1 lagdt2 date9.;     array x(*) lagdt1-lagdt2;   lagdt1=lag1(dt);   lagdt2=lag2(dt);     if first.id then count=1;   do i=count to dim(x);      x(i)=.;   end;   count+1;     if n(dt, lagdt1)=2 then diff1=dt-lagdt1;   if n(lagdt1, lagdt2)=2 then diff2=lagdt1-lagdt2;   run;   data consec1;   set have1;   if diff1=1 and diff2=1; run; proc sort data=consec1; by id dt; run; data dtrange; /*date ranges of 3 consecutive dates*/   set consec1;   by id dt;   if first.id;   mindt=lagdt2;   maxdt=dt;   keep id mindt maxdt; run;   data consec3;   merge have1 dtrange;   by id;   if mindt<=dt<=maxdt;   keep id dt amount; run;   proc sql;   create table want as   select id, dt, amount, sum(amount) as consec3_sum   from consec3   group by id; quit;   /*Comments: This works for 3 consecutive days. I don't think there is an easy way to do for x number of consecutive days since the lag for each would be different*/ ... View more

So I think you should try to fulfill the #2 requirement first.   data ds_a ds_b;  set have;  if rvw_type="A" then output ds_a;  if rvw_type="B" then output ds_b; run;   proc sort data=ds_a;   by employee_id t_date; run; /*Below dataset will give you one row per ID for the latest or last termination date*/ data ds_a1;   set ds_a;   by employee_id t_date;   if last.empoyee_id; run;   /* One you have dataset with single row per subject it becomes easier to merge and match*/ data ds_b1;   set ds_b;  /*rename the date variables in b for clarity*/   s_date_b=s_date;   t_date_b=t_date;   drop s_date t_date; run;   proc sort data=ds_b1; by employee_id; data match;    merge ds_a1 ds_b1;    by employee_id;      /*fulfill first condition here b date need to be within time window of a date*/   if s_date_b>=s_date and t_date_b<=t_date then unique_match_flag=1; run;     ... View more

I think the tricky part is the multiple rows of RVW_TYPE in this example for EMPLOYEE_ID=1 there are 4 rows for RVW_TYPE="A" and 3 rows for RVW_TYPE="B".   Which Review Type B Date are you trying to match? the Start date or Termination Date?  More Importantly which row in review type B would you choose? Earliest and Latest depends on which variable so latest Start date? or latest End date?   You can run the below code to look at the 2 datasets you are trying to match up. In merges you usually want primary keys with no repeat rows. So think about what you are trying to match. Since the A data seems to be the master data in your example, think about how to make the B data into single row per subject.    data ds_a ds_b;  set have;  if rvw_type="A" then output ds_a;  if rvw_type="B" then output ds_b; run;   ... View more

  data ds1;   set one;   /*seperate days and hours into 2 variables and convert to numeric*/   days=input(scan(day, 1, "days,"), best.);   hours=input(scan(scan(day, 2, "days,"), 1, " "), best.);   /*derive total hours using numeric variables*/   tothours=(days*24)+hours; run; ... View more

I'm not sure if there is any easy way to do this. The options I can think of are either change your comma seperator/deliminator to another character eg: ";" OR remove the comma from "5,000" ... View more

section 1) Creating a flag for each new instance of a rash on a different body part Instead of doing a loop I would approach this with a first.   proc sort data=have;   by ID Body_Part Rash_Date; run;   data want;   set have;   by ID Body_Part Rash_Date;   if first.Body_Part then New_Body_Part="Y"; run;   Section 2)  creating flag variable Repeat_Part when the hours difference between Rash date time and last drug adminstration date time for the same body Part if it is more than 50 hours then Repeat Part = "Y". There can be multiple rashes at different body Parts.   For this section you would need the variable for last drug administration date-time and merge it with original dataset. Then you can derive the number of hours between rash date-time and last drug administration date-time. I would approach this with a macro for each body part.   %macro repeat_fl(body_part=); data tmp;   set have;   if body_part="&body_part.";   if n(rash_date, drug_admin_date)=2 then hr_diff=intck('hour', rash_date, durg_admin_date);   if hr_diff>50 then repeat_part="Y"; run; %mend repeat_fl;            ... View more

So I think you'll want a dataset with all possible combinations of ID and DATE   proc sort data=have NODUPKEY out=uniq_id(keep=id); by id; run; proc sort data=have NODUPKEY out=uniq_date(keep=date); by date; run;   proc sql;   create table all_comb as   select uniq_id.id, uniq_date.date   from uniq_id cross join uniq_date; quit;   /*merge all possible combinations with original dataset*/ proc sort data=have; by id date; run; proc sort data=all_comb; by id date; run; data want;   merge all_comb(in=a) have;   by id date;   if a; run; ... View more

yes the sheet="" option in proc import is meant to bring in a specific sheet for excel. I think a macro would be your best bet if you have 50 workbooks. I am assuming you are using xlsx format in the below example. I am also assuming the the name of your second sheet in your excel workbook is "Sheet 2". It could have a different name if you named it differently but the default is "Sheet 1", "Sheet 2", etc.   %maco get_ds(xlin=,dsout=); proc import datafile="&xlin." out=&dsout. dbms=xlsx  replace; sheet="Sheet 2" run; %mend get_ds; %get_ds(xlin=excel_workbook1.xlsx, dsout=outdata1); %get_ds(xlin=excel_workbook2.xlsx, dsout=outdata2); etc.   ... View more

I think what you are looking for is a macro that gets the last value for each variable per ID. So something like the below:   %macro get_last(var=); proc sort data=test; by id; run;   data last_&var.;   by id;   set test;   if &var. ne .;   if last.id;   form_number=1;   last_&var.=&var.;   keep id form_number last_&var.; run; %mend get_last; %get_last(var=bp) %get_last(var=weight) %get_last(var=bc)   Afterwards you can merge these datasets back into the original TEST by ID FORM_NUMBER. Note: I'm not sure why there is more than 1 FORM_NUMBER. There can only a single last value. Are FORM_NUMBER 2,3,etc meant to be blank in the final dataset?   ... View more

I think this may have something to to with exposure adjusted incidence rates or exposure adjusted event rates. Since each group was exposed at different times you'll need an overall amount of exposure ( denominator usually done in person-years this is all subjects exposure time summed together both failure and success). The numerator would then be the number of people who failed times amount of exposure they had. ... View more

Hi, I think I've got a partial solution.  with retain statements you'll usually need a proc sort statement as well as a if first. for it to perform the way you want it to     you can do something similar with the other aperiods you need.      ... View more