topic Re: Set variables to match each other within a dataset in SAS Programming

Set variables to match each other within a dataset

CathyVI — Tue, 08 Feb 2022 21:14:14 GMT

Hi, I have this data and some names did not align with each other. Just like table 1 and I would like to have the names align just like in table 2. Any help please?

Table 1:

FName	LName	Sex	Age	Income	City	FNameA	LNameA	SexA	AgeA	IncomeA	BOD
John	Doe	M	20	100	KK	John	Doe		100	100	121298
Adam	Apple	F	21	300	BC	Peter	Cat	F		302	031288
John	Cloth	M	22	400	CU	Know	Know	M	400	412	030695
Eric	Dog	F	23	500	PO	Bill	Table			500	092287
Lin	Cat	M	45	300	YI	Lin	Cat	M	300	300	013076
Know	Know	F	46	201	HM	John	Cloth	F		201	070777
Bill	Table	F	50	330	VB	Adam	Apple		330	330	090999
Peter	Cat	F		302	OO	Eric	Dog	F	23	500	120388

Table 2:

FName	LName	Sex	Age	Income	City	FNameA	LNameA	SexA	AgeA	IncomeA	BOD
John	Doe	M	20	100	KK	John	Doe		100	100	121298
Adam	Apple	F	21	300	BC	Adam	Apple		330	330	090999
John	Cloth	M	22	400	CU	John	Cloth	F		201	070777
Eric	Dog	F	23	500	PO	Eric	Dog	F	23	500	PO
Bill	Table	F	50	330	VB	Bill	Table			500	092287
Lin	Cat	M	45	300	YI	Lin	Cat	M	300	300	013076
Know	Know	F	46	201	HM	Know	Know	M	400	412	030695
Peter	Cat	F		302	OO	Peter	Cat	F		302	031288

Re: Set variables to match each other within a dataset

PaigeMiller — Tue, 08 Feb 2022 21:18:32 GMT

Split table 1 in to table 1A (the first 6 columns) and table 1B (the next 6 columns).

Then do a join (or merge) of table 1A and table 1B by the person's first and last name.

UNTESTED CODE

proc sql;
    create table want as select a.*,b.* 
    from table1(keep=fname--city) as a left join table1(keep=fnamea--bod) as b 
    on a.fname=b.fnamea and a.lname=b.lnamea;
quit;

Re: Set variables to match each other within a dataset

ballardw — Tue, 08 Feb 2022 23:17:03 GMT

What does "did not align" mean?

If you combined two data sets then go back a step and show us how you combined them.