Hi Guys, I suspect that subjects are erranously assigned to control (group&=1) vs exposure(group&=2)  in the sample data attached. Actual data is 5 times this sample. Loss in follow up is reflected in the data as well. I'd like to test if groups are assigned to 1 and 2 consistently across four datasets (pretest=groupc, 7day follow up=group7d, 3mo follow-up=group3m and test among waitlisted=groupd). For that purpose, I thought, full join in proc sql joining all 4 datasets together on group and subj as key variables would help get better grasp of what happened. I don't mind to get rid of joining on where most incomplete variable is null (where groupc=null). Coz it might not work in this unbalanced data due to loss in follow up over time. Using SAS 9.4. The code didn't work. Log is shown below.   proc sql; create table outer as select a.groupc, a.subjc, b.groupd, b.subjd, c.group7d, c.subj7d, d.group3m, d.subj3m from groupc as a, groupd as b, group7d as c, group3m as d full join groupc on a.groupc = b.groupd and a.subjc = c.subjd full join groupc on a.groupc = c.group7d and a.subcj = c.subj7d full join groupd on b.groupd = c.group7d and b.subjd = c.subj7d full join group7d on c.group7d = d.group7d and c.subj7d = d.subj3m where a.group3m is null; quit;   Log:     617 proc sql; 618 create table outer as 619 select a.groupc, a.subjc, 620 b.groupd, b.subjd, 621 c.group7d, c.subj7d, 622 d.group3m, d.subj3m 623 from groupc as a, 624 groupd as b, 625 group7d as c, 626 group3m as d 627 full join groupc on a.groupc = b.groupd and a.subjc = c.subjd 628 full join groupc on a.groupc = c.group7d and a.subcj = c.subj7d 629 full join groupd on b.groupd = c.group7d and b.subjd = c.subj7d 630 full join group7d on c.group7d = d.group7d and c.subj7d = d.subj3m 631 where a.group3m is null; ERROR: Column subjd could not be found in the table/view identified with the correlation name C. ERROR: Column subjd could not be found in the table/view identified with the correlation name C. ERROR: Column subjd could not be found in the table/view identified with the correlation name C. ERROR: Column subjd could not be found in the table/view identified with the correlation name C. ERROR: Column subjd could not be found in the table/view identified with the correlation name C. ERROR: Column subcj could not be found in the table/view identified with the correlation name A. ERROR: Column subcj could not be found in the table/view identified with the correlation name A. ERROR: Column subcj could not be found in the table/view identified with the correlation name A. ERROR: Column subcj could not be found in the table/view identified with the correlation name A. ERROR: Column group7d could not be found in the table/view identified with the correlation name D. ERROR: Column group7d could not be found in the table/view identified with the correlation name D. ERROR: Column group3m could not be found in the table/view identified with the correlation name A. ERROR: Expression using equals (=) has components that are of different data types. 632 quit; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 0.01 seconds cpu time 0.01 seconds  Thank you very much for your time. proc export data=groupc outfile="\groupc.csv" dbms=csv; proc export data=groupd outfile="\groupd.csv" dbms=csv; proc export data=group7d outfile="\group7d.csv" dbms=csv; proc export data=group3m outfile="\group3m.csv" dbms=csv; run;   ... View more

Hi guys, Please help take below code out of macro.  I have to select certain disease cases from the data &file&year if found in the list &steps. Macros defined in the code below. I don't need double logics in macro as is now because I have a physical file prepped to replace &file&year now. I commented out the parts that I thought would not need. But it did not work out. I really have to get it out tonight. Crossing my fingers if anybody there for help.    data icd(drop=diag rename=x=icd); set icd; format x $10.; diag=compress(diag,'.'); diag_original=diag; x="[*]"||diag; run; proc sql noprint; select icd into :steps separated by '|' from icd; quit; %put &steps; options compress=yes; %let x=:Code_1 - :Code_24; /*****************************************************************************************************************/; libname red "C:\; %macro loop(case); %global case1; %let case1=&case; %if &case=shape %then %let condition = &steps; proc datasets noprint; delete all_&type._other; run; /*%do year = 2014 %to 2017; */ /*%if &year >= 2016 %then */ /* %do; */ /* libname red "C:...";*/ /* %if &type=Int %then %let file=&type.;*/ /* %else;*/ /* %if &type=Out %then %let file=&type._direct;*/ /* %end; */ data momentary(compress=yes); set red.&file&year(where=(patient_county_code in (58,33,37))); icd_code = catx('*','*',Diag_Code, of Code:); if prxmatch("m/&condition/oi",icd_code) > 0; source="In"; run; proc append base=all_&type._other(compress=yes) data=momentary force; run; %end; %mend loop; /*%let type=Int;/* %loop(shape); /*%let type=Out;/ %loop(shape);*   ... View more