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.
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.
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!
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.