topic Re: Merge matrices by ID variable in SAS/IML Software and Matrix Computations

Merge matrices by ID variable

kaushik_patra — Sun, 03 Feb 2019 22:08:48 GMT

I have two matrices A and ALS; my goal is to merge rows from ALS matrix where visit = 18 with matrix A by the ID variable. Any tip on how to do it in IML?

Re: Merge matrices by ID variable

VDD — Mon, 04 Feb 2019 01:01:26 GMT

/*
I have two matrices A and ALS; 
my goal is to merge rows from ALS matrix 
where visit = 18 with matrix A 
by the ID variable. 
Any tip on how to do it in IML?
*/
proc sort data=a out=a_sorted;
	by ID;
run;
proc sort data=als out=als_sorted;
	by ID;
run;
data want;
	merge a_sorted(in=ina)
	      als_sorted;
	 by ID;
	 if ina;
	 if visit=18;
run;

Re: Merge matrices by ID variable

Reeza — Mon, 04 Feb 2019 02:22:48 GMT

@kaushik_patra wrote:

I have two matrices A and ALS; my goal is to merge rows from ALS matrix where visit = 18 with matrix A by the ID variable. Any tip on how to do it in IML?

You've posted this in the IML forum, are you using SAS/IML or SAS Base. Are they matrices in IML or data sets?

It looks like you have you're trying to score some data, so you may want to consider PROC PLM and/or SCORE or a SCORE statement within whatever procedure you used.

Re: Merge matrices by ID variable

IanWakeling — Mon, 04 Feb 2019 08:54:41 GMT

If there are no complications, so both matrices are sorted on ID, the set of IDs is the same in both, and ALS contains a visit 18 for every ID, then you could use the LOC function to find the rows in ALS that correspond to the visit 18s.

A = A || ALS[ loc(ALS[,3]=18), ];

For more complex cases it may be better to do the merging in a data step as also suggested.

Re: Merge matrices by ID variable

kaushik_patra — Mon, 04 Feb 2019 13:27:10 GMT

Thank you.

Re: Merge matrices by ID variable

Reeza — Mon, 04 Feb 2019 15:42:39 GMT

@kaushik_patra please don't mark your own answer as correct, mark the answer that helped you solve the issue.

Re: Merge matrices by ID variable

kaushik_patra — Mon, 04 Feb 2019 16:13:19 GMT

Sorry, it's a mistake. Is there any way to undo and mark the correct answer provided by IanWakeling as solution?

Re: Merge matrices by ID variable

VDD — Mon, 04 Feb 2019 16:17:24 GMT

@kaushik_patra you can un-select the solution you chose and then reselect the answer that applies.