Hi, I made some changes as suggested and have attached the code below. It seems to work except it doesn't populate the corresponding Din numbers in the Din Field and excludes to original records with the OldDin in the Din Field. Otherwise, it seems to loop through and create the number of new records that I would like to be added to the previous record. Thanks. %MACRO INSERTS(EFF_DATE,EXP_DATE,PRODUCT,FIL); PROC SQL NOPRINT; CREATE TABLE mydata AS SELECT PLAN.* FROM WORK.PLAN WHERE ( PLAN.INC_FLAG = "Y" ) /* PRODUCT to be included */ AND ( PLAN.PRODUCT = "&PRODUCT" ) /* PRODUCT */ AND (PLAN.CID IN (1,3)) AND ( PLAN.EX_EXP_DATE ^= 99999999) AND (( PLAN.EX_EFF_DATE <= &EXP_DATE ) AND ( PLAN.EX_EXP_DATE >= &EFF_DATE )) ; PROC SORT DATA=mydata; BY CID PRODUCT ID EX_EFF_DATE EX_EXP_DATE APPLICATION_DATE; RUN; DATA WANT; SET mydata; BY CID PRODUCT ID EX_EFF_DATE EX_EXP_DATE APPLICATION_DATE; IF LAST.ID; RUN; PROC SORT DATA=mydata; BY CID PRODUCT ID; RUN; PROC SQL NOPRINT; SELECT COUNT(PRODUCT) INTO :COUNTS SEPARATED BY ' ' FROM WORK.DINS; QUIT; RUN; %PUT &COUNTS; PROC SQL NOPRINT; SELECT PRODUCT INTO :OBSERVATIONS SEPARATED BY ' ' FROM WORK.DINS; QUIT; RUN; %PUT &OBSERVATIONS; DATA &FIL; SET want; EX_EFF_DATE=&EFF_DATE; %DO I = 1 %TO &COUNTS; %PUT PRODUCT=%SCAN(&OBSERVATIONS,&I); PRODUCT = "&&PRODUCT&I"; OUTPUT; %END; RUN; PROC SORT DATA=&FIL NODUPKEY; BY CID ID PRODUCT EX_EFF_DATE APPLICATION_DATE; RUN; PROC SQL; CREATE TABLE Final AS SELECT &FIL..CID, &FIL..PRODUCT, &FIL..ID, &FIL..EX_EFF_DATE, &FIL..EX_EXP_DATE, (&LOAD_DATE) AS LOAD_DATE, &FIL..APPLICATION_DATE, FROM WORK.&FIL; QUIT; RUN; PROC SORT DATA=FINAL; BY CID PRODUCT ID; RUN; %MEND INSERTS; %INSERTS(&EFF_DATE1, &EXP_DATE1, &OLDDIN,&FIL1);
... View more
