topic Re: sas union value missing in SAS Enterprise Guide

sas union value missing

amar8580 — Wed, 08 Mar 2017 15:24:44 GMT

hi all

i am using the following code

PROC SQL;
CREATE TABLE PI AS
SELECT A.* FROM WORK.VRPT_PI_DAILY A
UNION
SELECT B.* FROM WORK.VRPT_PI_WEEKLY_FINAL B
UNION
SELECT C.* FROM WORK.VRPT_PI_MONTHLY C
UNION
SELECT D.* FROM WORK.VRPT_PI_QTR D;
QUIT;

now strange thing is happening....some of the values are missing for various numeric columns when i open the pi table

eg: revenue

but if i open the tables individually, i do see values in all 4 tables.

Re: sas union value missing

collinelliot — Wed, 08 Mar 2017 15:30:27 GMT

Are all your columns that you want to concatenate in the same position, same name? Have you tried "UNION CORR"?

Without seeing your data, it's hard to diagnose what's going on, but SAS is almost certainly doing what it's supposed to do.

Re: sas union value missing

amar8580 — Wed, 08 Mar 2017 15:47:42 GMT

yes they are the same columns and position is correct.

see attached screenshot

when i look at the weekly dataset, it shows value for TCA column

the moment i merge it, i see data for daily, monthly and quartely but for weekly TCA cell, the value is missing

Re: sas union value missing

collinelliot — Wed, 08 Mar 2017 16:01:06 GMT

In the image you attached, I see "...SER_CAR" before "TCA" in the top data and "..W_BPS" before "TCA" in the lower data. To me that suggests these columns are not aligned identically.

What if you use "UNION CORR"?

Re: sas union value missing

amar8580 — Wed, 08 Mar 2017 16:07:14 GMT

thanks a lot

my weekly dataset was missing columns

now its all good

Re: sas union value missing

collinelliot — Wed, 08 Mar 2017 16:11:15 GMT

Happy to help.

Re: sas union value missing

lkeyes — Wed, 08 Mar 2017 16:12:21 GMT

I noticed that in your screenshot that there is a column to the left of [TCA] in the VRPT_PI_WEEKLY_FINAL table that is something like [...W_BPS]; but in the constructed SQL table, it is [...SER_CAR]

Could that be an issue? Maybe i missed something about the order of the columns. But i see that you've selected (*) on all of your tables.

That would be my best guess??

Try a subset of specific columns, just to see if you get the appropriate records in there. And yeah, definitely use UNION CORR, seems to clear things up for me.