Hi. What is the protocol for follow-up questions if you have already marked an answer as a Solution? Start another entry? Anyway, KSHARP posted a solution for me that I can't expand successfully on (add another column). I have a table of NAME1-Values responses by month/year. I need to fill in rows around them to cover 11/2017 to the present month/year with a 0 value. Unfortunately, I get all combinations of NAME and LABEL with the filled-in rows. I only want the LABELS that go with NAME1 (see the bottom - LABEL13 should only be with NAME2). I hope that is clear enough. LABEL1 just needs to be duplicated 22 times (i.e. NAME1-LABEL1 (22 date rows), NAME1-LABEL2 (22 date rows), NAME1-LABEL4 (22 rows) etc.). (Sometimes there is more than one LABEL column if that matters. They all just need to be duplicated 22 times with their associated NAME....) Thanks! EXAMPLE FAILING OUTPUT: name label date value NAME1 LABEL1 11/2017 0 NAME1 LABEL1 12/2017 2 NAME1 LABEL1 01/2018 0 NAME1 LABEL1 02/2018 0 NAME1 LABEL1 03/2018 0 NAME1 LABEL1 04/2018 0 NAME1 LABEL1 05/2018 0 NAME1 LABEL1 06/2018 0 NAME1 LABEL1 07/2018 0 NAME1 LABEL1 08/2018 0 NAME1 LABEL1 09/2018 0 NAME1 LABEL1 10/2018 0 NAME1 LABEL1 11/2018 0 NAME1 LABEL1 12/2018 0 NAME1 LABEL1 01/2019 0 NAME1 LABEL1 02/2019 0 NAME1 LABEL1 03/2019 0 NAME1 LABEL1 04/2019 0 NAME1 LABEL1 05/2019 0 NAME1 LABEL1 06/2019 0 NAME1 LABEL1 07/2019 0 NAME1 LABEL1 08/2019 0 NAME1 LABEL1 09/2019 0 NAME1 LABEL13 11/2017 0 NAME1 LABEL13 12/2017 0 NAME1 LABEL13 01/2018 0 /* I have added code to code from KSHARP */ data have; input name $ label $ _date $ value; date=input(cats('01/',_date),ddmmyy10.); /* CATS removes all blanks */ drop _:; format date mmyys7.; datalines; NAME1 LABEL1 12/2017 2 NAME1 LABEL2 07/2018 1 NAME1 LABEL4 02/2019 3 NAME2 LABEL13 01/2018 3 NAME2 LABEL20 11/2018 4 NAME2 LABEL25 07/2019 1 ; data date1; /* this routine gives a column of dates */ do date='01nov2017'd to TODAY(); /* '01jul2019'd; */ if month(date) ne month then output; month=month(date); end; drop month; format date mmyys7.; run; proc sql; /* JOIN date column to Name and value */ create table want as select a.*,coalesce(b.value,0) as value from (select * from (select distinct name from have),(select distinct label from have), (select date from date1) ) as a left join have as b on a.name=b.name AND a.label=b.label AND a.date=b.date ; quit;
... View more