topic Re: merging two data sets by using two "by variables " in SAS Programming

merging two data sets by using two "by variables "

Ziba — Thu, 10 Jan 2019 17:03:08 GMT

hey all,

I want to merge two data sets that have a common variable called CIK, but in data set 2 I have CIK and CIK_1. However, the CIK in data set 1 could be CIK or CIK_1 which is not given separately like in data set 2. I was wondering if I can first merge them by CIK, and if there is no matching CIK, then compare CIK in data set one to CIK_1 in data set 2.

below is the simplified data sets 1 and 2.

data set 1 (SORTED BY cik)

Company name CIK

A 123456

B 654123

Data set 2 (sorted by CIK)

Company name CIK CIK_1

A 456789 123456

B 654123

I want the final merged data set looks like this:

Company name CIK

A 123456

B 654123

thanks in advance.

Ziba

Re: merging two data sets by using two "by variables "

novinosrin — Thu, 10 Jan 2019 17:17:06 GMT

straight forward sql


data one;
input Companyname  $   CIK $;
cards;
A                             123456
B                             654123
;
data two;
input Companyname  $   CIK     $         CIK_1 $;
cards;
A                            456789          123456
B                              .             654123
;

proc sql;
create table want as
select a.companyname,coalescec(a.cik ,CIK_1) as CIK
from one a left join two b
on a.cik=b.cik or a.cik=CIK_1;
quit;

Re: merging two data sets by using two "by variables "

Ziba — Fri, 11 Jan 2019 20:14:01 GMT

hey

I am sorry I forgot to mention in data set 1 i have more than two variables, and I need to grab them all (I need to grab all the variables in data set 2 as well). So I guess I need to use * to select all the variables?

I don`t know how to use sql and I am not sure why you have a and b in the code? should I create a and b in the first place or I dont have to?

Re: merging two data sets by using two "by variables "

novinosrin — Fri, 11 Jan 2019 20:16:42 GMT

Can you please expand your sample a little more with some blah blah, and i will copy/paste here and modify the sql

The reason is to avoid going back and forth

Re: merging two data sets by using two "by variables "

Ziba — Fri, 11 Jan 2019 21:00:09 GMT

In data set 1, I have variables up to 24 variables, and it includes company name and CIK.

in data set 2, I have variables up to 35, and it includes company name( it could be in the same format as data set 1, but I dont want them to merge into one column, so I used Company name_UCLA to differentiate), CIK, CIK_1.

so I think the desired merged data set should have 58 variables.

Should I provide you with all the variables?

Re: merging two data sets by using two "by variables "

novinosrin — Fri, 11 Jan 2019 21:18:03 GMT

Ok, try this and let me know.

Just a slight adjustment to get all vars from both


proc sql;
create table want(drop=_:) as
select a.*,b.*,coalescec(a._cik ,CIK_1) as CIK
from one(rename=(cik=_cik)) a left join two(rename=(cik=__cik)) b
on a._cik=b.__cik or a._cik=CIK_1;
quit;

Re: merging two data sets by using two "by variables "

novinosrin — Fri, 11 Jan 2019 21:20:47 GMT

Just play around, and seriously if you are not a lazy person like me you can ofcourse type select a.var1,a.var2,..a.varn and so on.

Re: merging two data sets by using two "by variables "

Ziba — Fri, 11 Jan 2019 23:12:32 GMT

proc sql;
create table want(drop=_:) as
select a.* from final.public_sort, b.* from final.ucla_sort ,coalescec(a._cik ,CIK_1) as CIK
from final.public_sort(rename=(cik=_cik)) a left join final.ucla_sort(rename=(cik=__cik)) b
on a._cik=b.__cik or a._cik=CIK_1;
quit;

I tried this code after I changed the names one and two into my sas data sets name.

But I have a syntax error pointing the star sign next to b:

433 proc sql;
434 create table want(drop=_:) as
435 select a.* from final.public_sort, b.* from final.ucla_sort ,coalescec(a._cik ,CIK_1) as CIK
-
22
76
ERROR 22-322: Expecting a name.

ERROR 76-322: Syntax error, statement will be ignored.

I am sure the code is correct, but Im not getting the results. Could you tell where I did it wrong?

Re: merging two data sets by using two "by variables "

novinosrin — Fri, 11 Jan 2019 23:24:56 GMT

proc sql;
create table want(drop=_:) as
select a.* f~~rom final.public_sort~~, b.* ~~from final.ucla_sort~~ ,coalescec(a._cik ,CIK_1) as CIK
from final.public_sort(rename=(cik=_cik)) a left join final.ucla_sort(rename=(cik=__cik)) b
on a._cik=b.__cik or a._cik=CIK_1;
quit;

Corrected version below:

proc sql;
create table want(drop=_:) as
select a.* , b.* ,coalescec(a._cik ,CIK_1) as CIK
from final.public_sort(rename=(cik=_cik)) a left join final.ucla_sort(rename=(cik=__cik)) b
on a._cik=b.__cik or a._cik=CIK_1;
quit;

Also, I took the sample as char values, if cik is a num num value, use coalesce instead of coalescec

Re: merging two data sets by using two "by variables "

Patrick — Sat, 12 Jan 2019 01:02:04 GMT

@Ziba

I believe it would help if you could post SAS datastep code which creates sample data. You don't need to create all variables but just enough so the problem to be solved becomes clear.

Re: merging two data sets by using two "by variables "

FreelanceReinh — Sat, 12 Jan 2019 13:42:07 GMT

@novinosrin wrote:

Also, I took the sample as char values, if cik is a num num value, use coalesce instead of coalescec

Just a side note: In PROC SQL you can always use the (older, ANSI SQL) function COALESCE, which works with character arguments as well as with numeric arguments (not with mixed types, though). As an additional advantage over the DATA step function COALESCEC (introduced in SAS 9, together with the DATA step function COALESCE) it assigns a suitable length to its results, not the default length of 200, which is mostly too long -- and when it's too short, it can even truncate the result (in PROC SQL without notice).

Re: merging two data sets by using two "by variables "

Ziba — Tue, 15 Jan 2019 20:47:54 GMT

The code worked, I appreciated your help. Thank you very much,