sas union value missing

[amar8580]

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.

[collinelliot]

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.

[amar8580]

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

[collinelliot · Accepted solution]

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"?

[amar8580]

thanks a lot

my weekly dataset was missing columns

now its all good

[collinelliot]

Happy to help.

[lkeyes]

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.