DATA Step, Macro, Functions and more

keep variable order when using length statement to merge data sets

Accepted Solution Solved
Reply
Super Contributor
Posts: 318
Accepted Solution

keep variable order when using length statement to merge data sets

I have several data sets and want to merge them.

 

data all7;
length  company $12.;
set demo_1 demo_2 demo_3 demo_4 demo_5 demo_6 demo_7;
run;

The company is the last variable in all the 7 data sets to be merged, but it has different lengths in them.

 

If I use length statement to set them to the same length, the variable becomes the first one in the merged data set.

 

One way to work around is to set the length in all the 7 data sets first before merging. I wonder if there is a way during merge.

thanks.


Accepted Solutions
Solution
‎02-22-2016 11:38 AM
Super User
Posts: 5,431

Re: keep variable order when using length statement to merge data sets

Posted in reply to fengyuwuzu
First this is an append/set operation not merge (which is similar to a SQL join).

What is the problem with variable order? It's just a physical thing. You usually only need to care about variable order in reports.

But, to answer your question, the only way I see is to declare all variables in a length. Or if you know which data set that has the appropriate length set that as first data set, or set it twice (the first time with obs=0) if you need to have a specific observation order.
Data never sleeps

View solution in original post


All Replies
Solution
‎02-22-2016 11:38 AM
Super User
Posts: 5,431

Re: keep variable order when using length statement to merge data sets

Posted in reply to fengyuwuzu
First this is an append/set operation not merge (which is similar to a SQL join).

What is the problem with variable order? It's just a physical thing. You usually only need to care about variable order in reports.

But, to answer your question, the only way I see is to declare all variables in a length. Or if you know which data set that has the appropriate length set that as first data set, or set it twice (the first time with obs=0) if you need to have a specific observation order.
Data never sleeps
Super Contributor
Posts: 318

Re: keep variable order when using length statement to merge data sets

you are right. The order of the variables only matters in final report.

I care it only because I have been used to the format, "visual effect" to my self.

 

as for the "merge" term, yes, this is a set operation (concatenation, like union in proc sql), not the speficially used merge term, which is like proc sql join. Thank you for the clarification.

Trusted Advisor
Posts: 1,117

Re: keep variable order when using length statement to merge data sets

[ Edited ]
Posted in reply to fengyuwuzu

Hello @fengyuwuzu,

 

I think, @LinusH mentioned the solution already: set a dataset twice.

data all7;
if 0 then set demo_1(drop=company); /* creates the PDV without COMPANY */
length company $12;
set demo_1 demo_2 demo_3 demo_4 demo_5 demo_6 demo_7;
run;

The above code assumes that all 7 datasets have the same variables. Otherwise, you could include more datasets into the first SET statement (which is never executed due to the IF condition).

 

Edit: This was also inspired by this recent post from @data_null__, who used this technique to prepare an array definition.

 

Super Contributor
Posts: 318

Re: keep variable order when using length statement to merge data sets

Posted in reply to FreelanceReinhard
Thank you so much, FreelanceReinhard.
Your code is so clear. Thanks!!
☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 263 views
  • 3 likes
  • 3 in conversation