topic Re: merge or set in SAS Programming

merge or set

Bal23 — Fri, 15 Jan 2016 19:19:20 GMT

my merge statment generates missing value for the first dataset

plesae see the attached two datasets. the real datasets are very long with many columns.

These two datasets were generated by sas macro.

Now I want to merge these two together.

data first;
set first1 first2;
by _NAME_;
run;
proc print data = first;
run;

the problem is,

the data generated actually put the first one into the first one, and the second one, the values are all missing

sample datasets attached

Re: merge or set

Steelers_In_DC — Fri, 15 Jan 2016 20:14:20 GMT

I just ran the following code, I'll paste my output below. Not sure what you are looking for but if that's the output you are getting I'd check the inputs:

data one;
infile cards dsd;
informat name $3. _1af _1af2 8.;
input name _1af _1af2;
cards;
c1a,1083,187
;run;

data two;
infile cards dsd;
informat name $3. _2af _2f2 8.;
input name$ _2af _2f2;
cards;
c1a,1083,187
;run;

data want;
set one two;
by name;
run;

name	_1af	_1af2	_2af	_2f2
c1a	1083	187
c1a			1083	187

Re: merge or set

Bal23 — Fri, 15 Jan 2016 20:32:21 GMT

Thanks. Now I understand why you are confused. No wonder why SAS is confused too.

i want to have a table

name  _af1  _af2
c1a       1086  187
c2a      1083   1

i need them to be vertically added, not horizontally appended
i hope it is clear enough

name	_1af	_1af2	_2af	_2f2
c1a	1083	187
c1a			1083	1

Re: merge or set

Steelers_In_DC — Fri, 15 Jan 2016 20:36:01 GMT

You need to change the variables in the second dataset:

data want;
set one

two(rename=(_2af=_1af _2f2=_1af2));
by name;
run;

Re: merge or set

Bal23 — Fri, 15 Jan 2016 20:48:56 GMT

Thank you very much. This works.

The problem is my original two tables are generated by sas macro. It is time consuming to change name one by one.

Would you please provide sas macro code?

Re: merge or set

ballardw — Fri, 15 Jan 2016 20:51:25 GMT

If you are generating the names in a macro then that would be the place to execute this code while the macro knows what they are.

Re: merge or set

Steelers_In_DC — Fri, 15 Jan 2016 21:41:46 GMT

I agree, manipulate this within the original macro to get the desired variable names.