topic Re: UNION question in SAS Programming

UNION question

greg6363 — Sun, 10 Jan 2021 01:50:50 GMT

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.

Re: UNION question

novinosrin — Sun, 10 Jan 2021 02:50:26 GMT

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!

Re: UNION question

PGStats — Sun, 10 Jan 2021 04:16:54 GMT

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.

Re: UNION question

greg6363 — Sun, 10 Jan 2021 06:11:51 GMT

It worked. Thanks so much.