topic Re: Merge datasets in SAS Programming

Merge datasets

lu_king — Tue, 06 Aug 2024 17:07:08 GMT

Hi all!

I have two datasets, but I want P1/P2 variable name in dataset B and it’s value to merge under the Target variable name and GcL1000 respectively in dataset A. How can I do this?

For example,
Dataset A
Date location target gcl gcl1000
25 Ab P1 .04 4.00
25 Ab P2 2.8 28.00
25 An P1 46.99 788.00
25 An P2 67.99 36.00

Data set B
Date location P1/P2
25 Ab 450.098
25 An 4774.995

The goal dataset would look something like this:

Date location target gcl gcl1000
25 Ab P1 .04 4.00
25 Ab P2 2.8 28.00
25 An P1 46.99 788.00
25 An P2 67.99 36.00
25 Ab P1/P2 450.098
25 An P1/P2 4774.995

Re: Merge datasets

lu_king — Tue, 06 Aug 2024 17:01:21 GMT

Re: Merge datasets

Tom — Tue, 06 Aug 2024 16:59:25 GMT

@lu_king wrote:
Edit: I want P1/P2 to merge under Target in dataset A!

No idea what you mean by "merge under". In SAS you merge BY some set of variables. So observations that match on the variables get merged into a single observation.

Perhaps by "under" you mean after? Perhaps instead of merging the dataset you just want to set them together? In that case you can use a BY statement to interleave the observation by the ID variables. The observations from the dataset listed last will appear "under" the observations for the other datasets within that set of BY values.

Re: Merge datasets

PaigeMiller — Tue, 06 Aug 2024 16:59:27 GMT

Please show us the desired output table. Honestly, I'm lost trying to follow your description of what you want.

Re: Merge datasets

Kurt_Bremser — Tue, 06 Aug 2024 17:21:31 GMT

Please post both datasets as working DATA steps with DATALINES.

What is the real name of that "P1/P2" variable? And into which variable of dataset A should these values be stored?

Re: Merge datasets

lu_king — Tue, 06 Aug 2024 18:14:58 GMT

data datasetA;
input Date $ Location $ Target $ gcl gcl1000;
datalines;
25 Ab P1 0.04 4.00
25 Ab P2 2.8 28.00
25 An P1 46.99 788.00
25 An P2 67.99 36.00
;
run;

data datasetB;
input Date $ Location $ P1_P2 $;
datalines;
25 Ab 450.098
25 An 4774.995
;
run;
The real name of P1/P2 is P1_P2, they’re protein locations that I’m averaging together. I’d like them to be stored under the Target variable in dataset A.

So that it reads in the merged dataset as follows

Data mergeddata;
input Date $ Location $ Target $ gcl gcl1000;
datalines;
25 Ab P1 0.04 4.00
25 Ab P2 2.8 28.00
25 An P1 46.99 788.00
25 An P2 67.99 36.00
25 Ab P1/P2 450.098
25 An P1/P2 4774.995
;
run;

Re: Merge datasets

Tom — Tue, 06 Aug 2024 23:42:58 GMT

So if you actually MERGE the two datasets:

data datasetA;
  input Date $ Location $ Target $ gcl gcl1000;
datalines;
25 Ab P1 0.04 4.00
25 Ab P2 2.8 28.00
25 An P1 46.99 788.00
25 An P2 67.99 36.00
;

data datasetB;
  input Date $ Location $ P1_P2 ;
datalines;
25 Ab 450.098
25 An 4774.995
;

data want;
  merge datasetA datasetB ;
  by date location ;
run;

You will get this dataset:

Obs    Date    Location    Target     gcl     gcl1000     P1_P2

 1      25        Ab         P1       0.04        4       450.10
 2      25        Ab         P2       2.80       28       450.10
 3      25        An         P1      46.99      788      4775.00
 4      25        An         P2      67.99       36      4775.00

What you propose as your desired output does not look like a DATASET. And the code you posted cannot run.

NOTE: SAS went to a new line when INPUT statement reached past the end of a line

If you just STACK the datasets:

data want2;
  set datasetA datasetB ;
run;

You can get something very similar to what I assume you wanted.

Obs    Date    Location    Target     gcl     gcl1000     P1_P2

 1      25        Ab         P1       0.04        4          .
 2      25        Ab         P2       2.80       28          .
 3      25        An         P1      46.99      788          .
 4      25        An         P2      67.99       36          .
 5      25        Ab                   .          .       450.10
 6      25        An                   .          .      4775.00

It would not be that hard to change the value of TARGET to P1/P2 in those last two observations.

data want2;
  set datasetA datasetB (in=inb);
  if inb then target='P1/P2';
run;

But I have no idea where you want to put the value of P1_P2 other than where it already is.

Re: Merge datasets

Kurt_Bremser — Wed, 07 Aug 2024 06:44:15 GMT

This should lead to your desired result:

data want;
set
  dataseta
  datasetb (
    in=inb
    rename=(p1_p2=gcl)
  )
;
by date location;
if inb then target = "P1/P2";
run;

Please test your codes before posting them; it's a matter of base courtesy.