topic Merge datasets with same variables, adding variables horizontally in SAS Programming

Merge datasets with same variables, adding variables horizontally

ANKH1 — Tue, 27 Jun 2023 21:29:55 GMT

We would like to merge two or more datasets that share the same variables (time, Type_a, Type_b, pair). These are two datasets:

DS1:

time	Type_a	Type_b	pair
time_3	1	1	1
time_6	2	0	0
time_9	0	1	0
time_12	1	1	1
time_18	1	1	1

DS2:

time	Type_a	Type_b	pair
time_3	2	1	1
time_6	1	0	0
time_9	1	1	1
time_15	0	2	0

We want to get the following output:

time	Type_a_ds1	Type_b_ds1	pair_ds1	Type_a_ds2	Type_b_ds2	pair_ds2
time_3	1	1	1	2	1	1
time_6	2	0	0	1	0	0
time_9	0	1	0	1	1	1
time_12	1	1	1	0	0	0
time_15	0	0	0	0	2	0
time_18	1	1	1	0	0	0

Notice that the datasets might not share the same values for time. As shown for time_15 (missing in DS1) and time_12 and time_18 (missing in DS2).

Re: Merge datasets with same variables, adding variables horizontally

Astounding — Tue, 27 Jun 2023 21:48:19 GMT

Just a few items to consider:

Why do this at all? The data appears to be useful in its current form.
Why keep TIME as a character variable? It would be more useful as numeric, and would then easily sort in the right order.
When there is no measurement (such as time_15 in DS1), why change missing values to a zero? Zero is a perfectly good measurement on other observations, so why use that to represent a missing value?

Re: Merge datasets with same variables, adding variables horizontally

ANKH1 — Tue, 27 Jun 2023 21:52:43 GMT

Thanks for your comments. How do you change time from character to numeric for it to sort? Ok, for the using "." instead of 0.

Re: Merge datasets with same variables, adding variables horizontally

Astounding — Tue, 27 Jun 2023 22:08:41 GMT

Changing TIME to numeric:

data new_ds1;
   set ds1;
   numtime = input(substr(time, 6), 3.);
run;

SUBSTR will start with the 6th character, and capture all remaining characters. INPUT converts character to numeric (here, and also in most cases).

Re: Merge datasets with same variables, adding variables horizontally

ANKH1 — Tue, 27 Jun 2023 22:12:23 GMT

Thanks. Do you have any suggestions about the merging of the two datasets to get the desired output?

Re: Merge datasets with same variables, adding variables horizontally

Reeza — Tue, 27 Jun 2023 22:21:20 GMT

Transpose each data set to a long data set instead.
Stack the datasets together adding the dataset names using the INDSNAME option.
Transpose back to wide, if necessary using the original variable name + datasetname to uniquely identify each column.

Re: Merge datasets with same variables, adding variables horizontally

ballardw — Wed, 28 Jun 2023 00:05:25 GMT

Generally a long form with a variable indicating the difference, i.e this record from set1 or set2 is quite often much more flexible.

If you really need to then the "trick" is to rename the variables with dataset options. Dummy code for generic response:

data want;
   set 
      ds1 (rename=(type_a=type_a_ds1 type_b=type_b_ds1 pair=pair_ds1))
      ds2 (rename=(type_a=type_a_ds2 type_b=type_b_ds2 pair=pair_ds2))
   ;
   by time;
run;

The rename as a data set option is done before the data is combined.

And that Time variable better sort properly...

Re: Merge datasets with same variables, adding variables horizontally

ANKH1 — Wed, 28 Jun 2023 00:34:35 GMT

Thanks! I used "rename".