UNION question

[greg6363]

I have a summary dataset that I group by a particular variable. There are days when a category may produce no records.  Since SAS does not produce a zero value output when no input data is generated, I created a second table with zero values for each category which I want to combine with the summary dataset to produce a complete output set that populates each category group (zeros for categories with no data).

Let me show the two datasets:

Set1                                                                  

Date    Category   Accts   Dollars

1/5/21   1-10          12       $1000

1/5/21   21-30        30       $9800

1/5/21   31-40        83       $15430

1/5/21   41-50        110      $23090

1/5/21    51-60        27       $8500

Set2

Category   Accts   Dollars

0               0           0

1-10          0           0

11-20        0           0

21-30        0           0

31-40        0           0

41-50        0           0

51-60        0           0

61-70        0           0

71-80        0           0

81-90        0           0

91-100       0           0

I want to use the UNION operator to join the two data sets that will keep the populated rows intact from Set 1 while bringing in the 

rows with the zero value categories from Set 2 that weren't populated in Set 1.  The output data set should look like Set 3 below:

Set 3

Date    Category   Accts   Dollars

1/5/21      0              0          0

1/5/21   1-10          12       $1000

1/5/21   11-20        0           0

1/5/21   21-30        30       $9800

1/5/21   31-40        83       $15430

1/5/21   41-50        110      $23090

1/5/21    51-60        27       $8500

1/5/21    61-70        0           0

1/5/21    71-80        0           0

1/5/21    81-90        0           0

1/5/21   91-100       0           0

I have enclosed the code I am using to perform this task but it's not producing the above output.

proc sql;
create table set3 as
select a.date, a.category, a.accts, a.dollars
from set1 a

group by a.category
having accts gt 0 and dollars gt 0
union
select b.category, b.accts, b.dollars,0
from set2 b;
quit;

From my query, the following errors are generated in the log file:

ERROR: Column 1 from the first contributor of UNION is not the same type as its counterpart
from the second.
ERROR: Column 2 from the first contributor of UNION is not the same type as its counterpart
from the second

Any assistance would be greatly appreciated.  Thanks.

[novinosrin]

Hi @greg6363  First off, Your problem is best addressed using format technique PRELOADFMT to include groups that are not available input dataset. It's a bit of learning curve, but very useful . I could demo here, however I'm awefully tired. I encourage you to google.

2ndly, For UNION or any SET operator to work, you need the position of the columns in both SELECT clauses to be of same datatype as SET operation is based on column positions rather than variable names.

To fix that, replace UNION with OUTER UNION CORR and see if that works. Best!

[PGStats · Accepted solution]

Use joins instead of set operations:

proc sql;
select 
    a.date format=yymmdd10., 
    b.category,
    coalesce(c.accts, 0) as accts,
    coalesce(c.dollars, 0) as dollars
from
    (select unique date from set1) as a cross join 
    (select unique category from set2) as b left join
    set1 as c on a.date=c.date and b.category=c.category;
quit;

Note that Set2 only needs the categories.

[greg6363]

It worked. Thanks so much.