CODEWITHSUNDEEP
mergesasdataset
Erica Jh Lee
Erica Jh Lee

Reputation: 121

SAS: Combining two data sets with different format

I have two datasets that are formatted differently

data1 looks like:

data1:

YYMM  test1
1101  98
1102  98
1103  94
1104  92
1105  99
1106  91

data 2 is just a single grand mean that looks like: 

data2:

GM
95

I would like to combine the two and have something that looks like this:

WANT:

YYMM  test1  GM
1101  98     95
1102  98     95
1103  94     95
1104  92     95
1105  99     95
1106  91     95

I'm sure there are different ways to go about configuring this but I thought I should make the 95 into a column and merge with data1.

Do I have to use macro for this simple task? Please show me some light!

Upvotes: 0

Views: 718

Answers (2)

Longfish
Longfish

Reputation: 7602

Here's a similar way that's slightly simpler.

data want;
set data1;
if _n_=1 then set data2;
run;

Upvotes: 0

mvherweg
mvherweg

Reputation: 1283

One straightforward way is to just merge without by statement and the use of retain:

data WANT (drop=temp);
    merge DATA1 DATA2 (rename=(GM=temp));
    retain GM;
    if _N_=1 then GM=temp;
run;

So basically you put the two datasets together. Because there is no by-statement, it will join together the first record of both datasets, the second record of both datasets and so on.

At the first record (if N=1), you grab the average and you put it in a variable for which the last value will be remembered (retain GM). So in record 2, 3 etc, the value will still be what you put into it at record 1.

To keep it all clean, i renamed your GM variable on the input, so it was available to use as name for the retained variable. And of course, i dropped the redundant variable.

You can also approach this issue with a macro variable or a proc sql. But better keep it simple.

Upvotes: 1

Related Questions