topic How to concatenate two files with some specific columns? in SAS Programming

How to concatenate two files with some specific columns?

Phil_NZ — Sun, 18 Apr 2021 23:37:05 GMT

Hi all,

I have two datasets named have1 and have2

Both datasets have 4 columns:

x1 x2 x3 c4

while x1,x3 are numeric and c is character. Now I only want to concatenate these two datasets without column x2 (because it was numeric in dataset have 1 but character in have2), I mean the concatenated dataset is

x1 x3 c4

My code to do concatenate in normal condition (without dropping x2) is:

data concatenate_;
length x1 8 x2 8 x3 8 c4 $100;
set have1 have2;
run;

Can you please help me to sort it out, thank you! Or in another word, how to concatenate two files with only some specific columns?

Warm regards.

Re: How to concatenate two files with some specific columns?

SASKiwi — Sun, 18 Apr 2021 23:33:39 GMT

data concatenate_ (keep = x1 x3 c4);
length x1 8 x2 8 x3 8 c4 $100;
set have1 have2;
run;

Re: How to concatenate two files with some specific columns?

Phil_NZ — Sun, 18 Apr 2021 23:36:12 GMT

Hi @SASKiwi

I see what you mean. Sorry I did not explain well. What I want is I do not want SAS to perform concatenating on x2 (because the types of x2 are mixed: it was numeric in have1 but character in have2)..

Thank you!

Re: How to concatenate two files with some specific columns?

Patrick — Sun, 18 Apr 2021 23:51:58 GMT

Below some options

data concatenate_;
length x1 8 x3 8 c4 $100;
set have1(keep=x1 x3 c4) have2 (keep=x1 x3 c4);
run;

Or this way:

proc sql;
  create table combined as
  select x1,x3,c4
  from have1
  union all corr
  select x1,x3,c4
  from have2
  ;
quit;

Or if you want to keep x2 as character (in example below assumed numeric in have2)

proc sql;
  create table combined as
  select x1,x3,c4, x2
  from have1
  union all corr
  select x1,x3,c4, put(x2, 32.) as x2 length=16
  from have2
  ;
quit;

or you could also convert the character to numeric if the data allow for it

proc sql;
  create table combined as
  select x1,x3,c4, input(x2, best32.) as x2
  from have1
  union all corr
  select x1,x3,c4, x2
  from have2
  ;
quit;

and to add to above: Using a SAS UNION will for character variables in the target table use the max length of the variable coming from the sources so you never risk data truncation if lengths differ.