https://communities.sas.com/t5/SAS-Programming/How-to-create-dummy-data-using-SQL-when-the-counts-are-missing/m-p/719375#M222743 <P>I need to create a table with overall counts, last weeks counts as well as daily counts( Sunday to Saturday).</P> <P>Some time my daily counts table is failing because there is no data for Sunday? How can I handle this?</P> <P>&nbsp;</P> <P>data have;<BR />input product description $ total last_week day :date9. amount_received;<BR />cards;<BR />1235 Item1 3173 1070 07FEB2021 73<BR />1235 Item1 3173 1070 08FEB2021 34<BR />5432 Item4 340 96 07FEB2021 19<BR />5432 Item4 340 96 08FEB2021 2<BR />6574 Item5 5 2 08FEB2021 3<BR />;<BR />proc sql;<BR />create table track_daily as<BR />select product, description, Day format=date9., sum(amount_received) as Received<BR />from have<BR />group by product, description, Day;<BR />quit;</P> Mon, 15 Feb 2021 16:37:05 GMT Stalk 2021-02-15T16:37:05Z https://communities.sas.com/t5/SAS-Programming/How-to-create-dummy-data-using-SQL-when-the-counts-are-missing/m-p/719375#M222743 <P>I need to create a table with overall counts, last weeks counts as well as daily counts( Sunday to Saturday).</P> <P>Some time my daily counts table is failing because there is no data for Sunday? How can I handle this?</P> <P>&nbsp;</P> <P>data have;<BR />input product description $ total last_week day :date9. amount_received;<BR />cards;<BR />1235 Item1 3173 1070 07FEB2021 73<BR />1235 Item1 3173 1070 08FEB2021 34<BR />5432 Item4 340 96 07FEB2021 19<BR />5432 Item4 340 96 08FEB2021 2<BR />6574 Item5 5 2 08FEB2021 3<BR />;<BR />proc sql;<BR />create table track_daily as<BR />select product, description, Day format=date9., sum(amount_received) as Received<BR />from have<BR />group by product, description, Day;<BR />quit;</P> Mon, 15 Feb 2021 16:37:05 GMT https://communities.sas.com/t5/SAS-Programming/How-to-create-dummy-data-using-SQL-when-the-counts-are-missing/m-p/719375#M222743 Stalk 2021-02-15T16:37:05Z https://communities.sas.com/t5/SAS-Programming/How-to-create-dummy-data-using-SQL-when-the-counts-are-missing/m-p/719463#M222759 <P>Wouldn't it be better to change your processing to handle missing days rather than insert dummy data?</P> <P>&nbsp;</P> <P>Please post the SAS log of the code that is failing.</P> Mon, 15 Feb 2021 19:44:31 GMT https://communities.sas.com/t5/SAS-Programming/How-to-create-dummy-data-using-SQL-when-the-counts-are-missing/m-p/719463#M222759 SASKiwi 2021-02-15T19:44:31Z