The SAS Output Delivery System and reporting techniques

How to insert missing records into a table?

Accepted Solution Solved
Reply
Contributor
Posts: 36
Accepted Solution

How to insert missing records into a table?

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

store_namesales

store1

50
store260
store470
store655

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

store_namesales

best

50
store260
store30
store470
store50
store655

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

Thanks.


Accepted Solutions
Solution
‎11-07-2012 02:21 PM
Frequent Contributor
Posts: 129

Re: How to insert missing records into a table?

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.

View solution in original post


All Replies
Solution
‎11-07-2012 02:21 PM
Frequent Contributor
Posts: 129

Re: How to insert missing records into a table?

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.

Contributor
Posts: 36

Re: How to insert missing records into a table?

Posted in reply to LarryWorley

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.Smiley Happy

Bruce

SAS Super FREQ
Posts: 8,864

Re: How to insert missing records into a table?

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;

Contributor
Posts: 36

Re: How to insert missing records into a table?

Posted in reply to Cynthia_sas

Thanks Cynthia,

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

Bruce

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 356 views
  • 0 likes
  • 3 in conversation