How to insert missing records into a table?

[BigD]

I have this situation where I generate a simple two column table like so:

store_name	sales
store1	50
store2	60
store4	70
store6	55

However in the report I need to have the values for store3 and store5 set to zero like below:

store_name	sales
best	50
store2	60
store3	0
store4	70
store5	0
store6	55

I do have all the names of all the stores that need to be put in the report.

Thanks.

[LarryWorley · Accepted solution]

Assuming you have a dataset which consists of the list of storenames, you can do this with either a data step merge or proc sql left join.  Here is example code:

Data table_for_report;

   merge table_list (keep = store_name)

             sales_table(keep = store_name sales)

             ;

   by store_name ;

   if sales = . then sales = 0 ;

run ;

It is important that the table_list be the first table in the merge statement.  A record will be create for each value of store_name which is in either table.  The table will have a sales column which will be filled with the sales values from the second table when that store is in the sales table.  otherwise it will have a missing value.  Then the if statement converts the missing value to a value of 0.

[BigD]

Thanks Larry,

I can't tell you how many times I'm deleting extra records that I don't need from merges, and here they are the solution.

Bruce

[Cynthia_sas]

Hi:

  In addition, if you use PROC REPORT (or PROC TABULATE) you can get rows populated automatically to 0 by using simple procedure options. See the output from PROC REPORT below in #1. The data does not have any 16 year olds where SEX=F. Yet, because I use the COMPLETEROWS option, the row with 0 for 16 and F is put into the table automatically (without needing an external DATA step program).

  Or, if I have a user-defined FORMAT, then I can use COMPLETEROWS with PRELOADFMT (see the rows for 17 and 18 in the output #2). PROC TABULATE has similar, but slightly different syntax available.

cynthia

ods html file='c:\temp\report_completerows.html' style=sasweb;

proc report data=sashelp.class nowd completerows;

  title '1) Use COMPLETEROWS only';

  column sex age n pctn;

  define sex / group;

  define age / group;

  define n / 'Count';

  define pctn / 'Percent' f=percent9.;

run;

proc format;

  value agef 11='11'

             12='12'

             13='13'

             14='14'

             15='15'

             16='16'

             17='17'

             18='18';

run;

proc report data=sashelp.class nowd completerows;

  title '2) Use PRELOADFMT with COMPLETEROWS';

  column sex age n pctn;

  define sex / group;

  define age / group f=agef. preloadfmt;

  define n / 'Count';

  define pctn / 'Percent' f=percent9.;

run;

ods html close;

[BigD]

Thanks Cynthia,

I'll have to remember this for next time...

Bruce