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.
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.
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.
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
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;
Thanks Cynthia,
I'll have to remember this for next time...
Bruce
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.