Merge datasets

[lu_king]

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

[Tom]

---

@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.

[PaigeMiller]

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

[Kurt_Bremser]

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?

[lu_king]

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;

[Tom]

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.

[Kurt_Bremser]

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.