BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
BigD
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
LarryWorley
Fluorite | Level 6

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

4 REPLIES 4
LarryWorley
Fluorite | Level 6

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
Calcite | Level 5

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

Cynthia_sas
SAS Super FREQ

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
Calcite | Level 5

Thanks Cynthia,

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

Bruce

sas-innovate-2024.png

Today is the last day to save with the early bird rate! Register today for just $695 - $100 off the standard rate.

 

Plus, pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 3789 views
  • 1 like
  • 3 in conversation