It's feasible but it's non-sense because the code becomes unreadable PROC SQL; CREATE TABLE WORK.BASE(DROP=PLANT_DESC CNV_FCTR ALTN_UOM TGT_QTY BASE_UOM PLANNED_ZNL2 _ZNL_hlp) AS SELECT * ,A.TGT_QTY*B.CNV_FCTR AS PLANNED_ZNL2 ,case when _ZNL_hlp = . THEN PLANNED_ZNL2 else _ZNL_hlp end as _ZNL FROM WORK.BASELINE AS A LEFT JOIN WORK.AT_UOM(rename=(_ZNL=_ZNL_hlp)) AS B ON A.MATRL_NBR = B.MATRL_NBR AND A.BASE_UOM =B.BASE_UOM AND B.ALTN_UOM = 'ZNL'; QUIT; you need to rename the _ZNL variable because you can't add it twice to the dataset. (select *) @David_Billa make sure you always terminate proc sql with QUIT; ... View more

I've adapted the code so that scenario 1 would come into play with this data. data have; input USUBJID $ VISIT $ VISITNUM AVISIT $ AVISITN PARAM $ AVAL AWTARGET ADY AWDIFF; datalines; 101 WEEK1 25 Week1 20 PAR1 55 5 4 1 101 WEEK1 25 Week1 20 PAR1 50 5 3 2 102 WEEK1 25 Week1 20 PAR1 55 5 4 1 102 WEEK1 25 Week1 20 PAR1 50 5 4 1 103 UNS 20.01 Week1 20 PAR1 55 5 5 0 103 UNS 20.02 Week1 20 PAR1 50 5 5 0 103 UNS 20.03 Week1 20 PAR1 50 5 5 0 105 Week20 20.01 Week1 20 PAR1 55 141 169 28 105 UNS20.02 20.02 Week1 20 PAR1 50 141 169 28 105 Week44 20.03 Week1 20 PAR1 50 5 5 0 ; run; proc sort data=have; by usubjid param avisitn descending AWDIFF; run; data want; set have; length DTYPE $40; call missing(dtype); by usubjid param avisitn descending AWDIFF; *Define help vars; retain n . countflg . avgn . avgsum . avgcalc . avisitnchg .; *SC1; if first.AWDIFF then countflg=0; countflg+1; if last.avisitn and countflg=1 then flg='Y'; *SC2/3; lag1_avisitn=lag1(avisitn); if first.avisitn then do; avgn=0; avgsum=0; avgcalc=0; avisitnchg=0; lag1_avisitn=.; end; avgn+1; if avisitn ne lag1_avisitn then avisitnchg+1; if not missing(aval) then avgsum=sum(avgsum,aval); avgcalc=avgsum/avgn; if last.avisitn and avgn>1 and countflg=avgn and missing(flg) then do; output; *SC2/3; avg=avgcalc; dtype='AVG'; *SC3; if avisitnchg=avgn then avisitn=999; output; end; else output; drop n countflg lag1_avisitn avgn avgsum avgcalc avisitnchg; run; ... View more

you could do something like this: data have; input USUBJID $ VISIT $ VISITNUM AVISIT $ AVISITN PARAM $ AVAL AWTARGET ADY AWDIFF; datalines; 101 WEEK1 25 Week1 20 PAR1 55 5 4 1 101 WEEK1 25 Week1 20 PAR1 50 5 3 2 102 WEEK1 25 Week1 20 PAR1 55 5 4 1 102 WEEK1 25 Week1 20 PAR1 50 5 4 1 103 UNS 20.01 Week1 20.01 PAR1 55 5 5 0 103 UNS 20.02 Week1 20.02 PAR1 50 5 5 0 103 UNS 20.03 Week1 20.03 PAR1 50 5 5 0 ; run; proc sort data=have; by usubjid param avisit descending AWDIFF; run; data want; set have; length DTYPE $40; call missing(dtype); by usubjid param avisit descending AWDIFF; *Define help vars; retain n . countflg . avgn . avgsum . avgcalc . avisitnchg .; *SC1; if first.AWDIFF then countflg=0; countflg+1; if last.avisit and countflg=1 then flg='Y'; *SC2/3; lag1_avisitn=lag1(avisitn); if first.avisit then do; avgn=0; avgsum=0; avgcalc=0; avisitnchg=0; lag1_avisitn=.; end; avgn+1; if avisitn ne lag1_avisitn then avisitnchg+1; if not missing(aval) then avgsum=sum(avgsum,aval); avgcalc=avgsum/avgn; if last.awdiff and avgn>1 and countflg=avgn then do; output; *SC2/3; avg=avgcalc; dtype='AVG'; *SC3; if avisitnchg=avgn then avisitn=999; output; end; else output; drop n countflg lag1_avisitn avgn avgsum avgcalc avisitnchg; run; ... View more

what should the avisitn be for scenario 3? ... View more

Here you go: FILENAME Statement: URL Access Method  ... View more

Check if you can run a SQL-PASS-THROUGH Query so you'd not have to change the code   ... View more

First if you do not need the summary statistics in myoutfile then run proc means with 'nway' and 'noprint' option. Then run a proc report on myoutfile to fine tune the display. proc means data=sashelp.class nway noprint; var weight; class sex age; output out= myoutfile mean=meanresult stderr=standerr; run; proc sort data=myoutfile; by age sex;run; data myoutfile; set myoutfile; order+1; run; proc report data=myoutfile; columns order sex age meanresult standerr; define order / order order=data noprint; define sex / display; define age / display; run;   ... View more

Minimize the question to its simplest form and perform a test run: proc sql; select * from sashelp.class ; *where 1 eq 2 and 2 eq 3; quit; ... View more

hi, the results are ok, the diagnosis year is not printed for a cleaner view on the results, it's only printed when it changes. F 33 5 1 relates to the diagnosis year 2011. ... View more

Still not clear to me why you need a macro for which operations and results exactly.  I think posting portion of your job could be a better alternative rather than trying to describe it. Based on your comment So for example, if tp_1 = t1 and tp_2 = t1, I need a macrotized way of pointing SAS to vlist1_tp_1_t1 and vlist_tp_2_t1 for that record.  You mean like this?     %let tp_1=t1; %let tp_2=t4; %let SASpoint1=vlist1_tp_1_&tp_1.; %let SASpoint2=vlist2_tp_2_&tp_2.; %put &=SASpoint1; %put &=SASpoint2; *or this?; data _NULL_; length tp_1 tp_2 $10; tp_1='t1'; tp_2='t1'; call symputx('SASpoint3',cats('vlist1_tp_1_',tp_1)); call symputx('SASpoint4',cats('vlist1_tp_1_',tp_2)); run; %put &=SASpoint3; %put &=SASpoint4; %symdel SASpoint1 SASpoint2 SASpoint3 SASpoint4; ... View more

Hi, it's not clear to me what you're exactly trying to compare. You mean something like this? data have; input TP_1 $ TP_2 $ ; datalines; t1 t4 t2 t3 t3 t2 t1 t1 t2 t4 t3 t3 ; run; %macro test(inds=,outds=,timepoint1=,timepoint2=); %if &timepoint1. ne &timepoint2. and &timepoint1. ne and &timepoint2. ne %then %do; data &outds.; set &inds.; if &timepoint1. eq &timepoint2. then put "NOTE: " &timepoint1.= "eq" &timepoint2.= "at" _N_=; run; %end; %mend test; %test(inds=have,outds=want,timepoint1=TP_1,timepoint2=TP_2); ... View more

your defined length is too short. remove the dot in your length specification btw., you're defining a length, not a format. ... View more