topic Re: keep variable order when using length statement to merge data sets in SAS Programming

keep variable order when using length statement to merge data sets

fengyuwuzu — Mon, 22 Feb 2016 16:19:05 GMT

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.

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

LinusH — Mon, 22 Feb 2016 16:28:00 GMT

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.

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

fengyuwuzu — Mon, 22 Feb 2016 16:34:37 GMT

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.

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

FreelanceReinh — Mon, 22 Feb 2016 17:37:27 GMT

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.

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

fengyuwuzu — Mon, 22 Feb 2016 16:39:07 GMT

Thank you so much, FreelanceReinhard.
Your code is so clear. Thanks!!