topic Re: Need help with merging two different data sets in SAS Programming

Need help with merging two different data sets

karanfil — Mon, 29 Dec 2025 18:03:06 GMT

Hello All,

I have two data sets as below. Data sets have different number of observations and one common variable "TICKER". What is the best SAS code to merge them. Thanks.

DATA A

TICKER	YEARP	BSIZEP
ACGN	2019	8
ACRS	2019	8
ADAP	2019	10

DATA B

TICKER	YEAR	DBSIZE
ACGN	2020	7
ACGN	2021	7
ACMR	2021	5
ACRS	2020	9
ADAP	2020	9

Re: Need help with merging two different data sets

Kurt_Bremser — Mon, 29 Dec 2025 18:35:50 GMT

Please do also show the intended result of the merge.

Re: Need help with merging two different data sets

karanfil — Mon, 29 Dec 2025 20:29:45 GMT

Thanks for the response. I would like to have the following data sets after merging. And run a paired T-test. (like between BSIZEP and DBSIZE) Data A has a YEARP observation during 2018-2019, and data B has a Year 2020-2021 observation. I really appreciate the help.

DATA A AND DATA B MERGED
TICKER	YEARP	BSIZEP	TICKER	YEAR	DBSIZE
ACGN	2019	8	ACGN	2020	7
.	.	.	ACGN	2021	7
.	.	.	ACMR	2021	5
ACRS	2019	8	ACRS	2020	9
ADAP	2018	10	ADAP	2020	9

Re: Need help with merging two different data sets

Patrick — Tue, 30 Dec 2025 00:41:25 GMT

Not really sure if that's the ideal data structure for your t-test but below what you've been asking for.

Please note that I've changed the year in the last row for data A so it matches what you show us as desired result.

data A;
    infile datalines dsd truncover dlm=' ';
    length TICKER $5;
    input TICKER $ YEARP BSIZEP;
datalines;
ACGN 2019 8
ACRS 2019 8
ADAP 2018 10
;
run;

proc sort data=a;
  by ticker yearp;
run;

data B;
    infile datalines dsd truncover dlm=' ';
    length TICKER $5;
    input TICKER $ YEAR DBSIZE;
datalines;
ACGN 2020 7
ACGN 2021 7
ACMR 2021 5
ACRS 2020 9
ADAP 2020 9
;
run;

proc sort data=b;
  by ticker year;
run;

data want;
  merge a b;
  by ticker;
  output;
  call missing(of _all_);
run;

proc print data=want;
run;

Re: Need help with merging two different data sets

Tom — Tue, 30 Dec 2025 01:09:13 GMT

@karanfil wrote:

Thanks for the response. I would like to have the following data sets after merging. And run a paired T-test. (like between BSIZEP and DBSIZE) Data A has a YEARP observation during 2018-2019, and data B has a Year 2020-2021 observation. I really appreciate the help.

DATA A AND DATA B MERGED

TICKER YEARP BSIZEP TICKER YEAR DBSIZE

ACGN 2019 8 ACGN 2020 7

. . . ACGN 2021 7

. . . ACMR 2021 5

ACRS 2019 8 ACRS 2020 9

ADAP 2018 10 ADAP 2020 9

That result does not make much sense.

How can you have two variables with the same name?

Why did you eliminate the value of BSIZEP from one of the observations?

Did you mean you wanted this:

If so then just just merge by TICKER.

data want;
  merge a b ;
  by ticker;
run;

Note that both datasets will need to be sorted by TICKER before you can use the MERGE statement.

You can use PROC TTEST to run a paired t-test.

proc ttest data=want;
   paired BSIZEP*DBSIZE;
run;

But note that will use only four of the observations since one of them does not have the BSIZEP value. And t-test is is not the right statistic for that dataset since the same TICKER value can have multiple observations. And those observations all have the same value of BSIZEP.

Re: Need help with merging two different data sets

karanfil — Tue, 30 Dec 2025 15:37:47 GMT

Thanks, Patrick. It is helpful. Do you know what the best mean difference test is? DATA A has fewer observations than DATA B. DATA B has companies that DATA A has and also companies that DATA A does not have. Any thought?

Thanks again

Re: Need help with merging two different data sets

karanfil — Tue, 30 Dec 2025 15:45:03 GMT

Thanks Tom for help.

Re: Need help with merging two different data sets

Ksharp — Wed, 31 Dec 2025 11:43:07 GMT

If you want to compare the mean from A table and B table, then it has nothing to do with Paired-T test. You could just use simple T test or non-parameter version T test (Wilcoxon Test - Check PROC NPAR1WAY) .
But for you this special case, you have a YEAR variable, that seems it is a panel data or repeated measure data. You need to use Mixed model, check PROC MIXED or PROC GLIMMIX .

And if you have SAS/ETS module, also could use PROC PANEL , but that is a Time Series Analysis issue, and post your question at Forecasting Forum:
https://communities.sas.com/t5/SAS-Forecasting-and-Econometrics/bd-p/forecasting_econometrics