Combine tables vertically taking only common variables in simple steps

Reply
Occasional Contributor
Posts: 6

Combine tables vertically taking only common variables in simple steps

[ Edited ]

   As question mentioned, I have several tables with product IDs variables from different time, a lot of which are in common. However, some of each contains ID that the others do not contain.

   I want to combine them vertically based on their time, but only take the common variables in all the tables. Is there a simple step I can do that instead of checking for missing data in the new table everytime I combine them? Thank you!

Trusted Advisor
Posts: 1,117

Re: Combine tables vertically taking only common variables in simple steps

Posted in reply to psychopedia

Hello @psychopedia,

 

Do you mean something like this?

/* Create test data */

data have1;
array x year a b c d e (2011 10:14);
run;

data have2;
array x year b c d e f g (2012 20:25);
run;

data have3;
array x year c d e f g h (2013 30:35);
run;

/* Combine tables vertically, restricted to common variables */

proc sql;
create table want as
select * from have1 union all corr
select * from have2 union all corr
select * from have3;
quit;

proc print data=want noobs;
run;

You may want to check the sort order in dataset WANT and possibly add an ORDER BY clause if you're not satisfied.

Occasional Contributor
Posts: 6

Re: Combine tables vertically taking only common variables in simple steps

Posted in reply to FreelanceReinhard

Thank you! This is what I m looking for. Really appreciate it.

Ask a Question
Discussion stats
  • 2 replies
  • 240 views
  • 0 likes
  • 2 in conversation