topic Re: How to insert missing records into a table? in ODS and Base Reporting

How to insert missing records into a table?

BigD — Wed, 07 Nov 2012 18:55:11 GMT

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.

Re: How to insert missing records into a table?

LarryWorley — Wed, 07 Nov 2012 19:21:15 GMT

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.

Re: How to insert missing records into a table?

Cynthia_sas — Wed, 07 Nov 2012 21:23:45 GMT

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;

Re: How to insert missing records into a table?

BigD — Thu, 08 Nov 2012 13:23:47 GMT

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

Re: How to insert missing records into a table?

BigD — Thu, 08 Nov 2012 13:25:47 GMT

Thanks Cynthia,

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

Bruce