BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
amar8580
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
collinelliot
Barite | Level 11

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

6 REPLIES 6
collinelliot
Barite | Level 11

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
Calcite | Level 5

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
collinelliot
Barite | Level 11

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
Calcite | Level 5

thanks a lot

my weekly dataset was missing columns

now its all good

 

collinelliot
Barite | Level 11

Happy to help.

lkeyes
Obsidian | Level 7

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. 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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