Desktop productivity for business analysts and programmers

sas union value missing

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

sas union value missing

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.


Accepted Solutions
Solution
‎03-08-2017 11:07 AM
PROC Star
Posts: 295

Re: sas union value missing

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

View solution in original post


All Replies
PROC Star
Posts: 295

Re: sas union value missing

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.

Occasional Contributor
Posts: 9

Re: sas union value missing

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

 


union_table.PNG
Solution
‎03-08-2017 11:07 AM
PROC Star
Posts: 295

Re: sas union value missing

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

Occasional Contributor
Posts: 9

Re: sas union value missing

thanks a lot

my weekly dataset was missing columns

now its all good

 

PROC Star
Posts: 295

Re: sas union value missing

Happy to help.

Occasional Contributor
Posts: 10

Re: sas union value missing

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 CORRseems to clear things up for me. 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 215 views
  • 2 likes
  • 3 in conversation