topic Re: Merging by row in New SAS User

Merging by row

Epid — Thu, 20 Feb 2020 09:41:48 GMT

Dear Experts,

I want to delete the data in red font by matching D2010-D2013 to P2010-P2013. For example, no data is in D2011, so delete P2011.

Country	D2010	D2011	D2012	D2013	P2010	P2011	P2012	P2013
A	5		10		5600	5665	5800	6000
B	5	15	20		4200	4255	4300	4355
C	3			9	4255	4236	4560	4590

Then finally want the following table:

Country	D2010	D2011	D2012	D2013	P2010	P2011	P2012	P2013
A	5		10		5600		5800
B	5	15	20		4200	4255	4300
C	3			9	4255			4590

Could you please help me, what SAS code I can use?

Thanks,

Epid

Re: Merging by row

ed_sas_member — Thu, 20 Feb 2020 09:54:57 GMT

Hi @Epid

Here is a way to do that, using arrays:

data have;
	infile datalines dlm="09"x dsd missover;
	input Country $ D2010	D2011	D2012	D2013	P2010	P2011	P2012	P2013;
	datalines;
A	5	 	10	 	5600	5665	5800	6000
B	5	15	20	 	4200	4255	4300	4355
C	3	 	 	9	4255	4236	4560	4590
;
run;

data want;
	set have;
	array _D (*) D2010-D2013;
	array _P (*) P2010-P2013;
	do i=1 to dim(_D);
		if _D(i) = . then _P(i)=.;
	end;
	drop i;
run;

Re: Merging by row

PeterClemmensen — Thu, 20 Feb 2020 09:56:15 GMT

One way

data have;
input Country $ D2010-D2013 P2010-P2013;
infile datalines dlm='|';
datalines;
A|5|  |10| |5600|5665|5800|6000
B|5|15|20| |4200|4255|4300|4355
C|3|  |  |9|4255|4236|4560|4590
;

data want;
    set have;
    array d D2010-D2013;
    array p P2010-P2013;
    do over d;
        if d = . then p = .;
    end;
run;

Re: Merging by row

Kurt_Bremser — Thu, 20 Feb 2020 10:04:46 GMT

By using a better data structure, the code becomes dead simple, and you'll save disk space on top:

(using the example data as provide by the data steps others already posted)

proc transpose data=have out=trans;
by country;
var d: p:;
run;

data have_long;
merge
  trans (rename=(col1=d) where=(substr(_name_,1,1) = 'D'))
  trans (rename=(col1=p) where=(substr(_name_,1,1) = 'P'))
;
period = substr(_name_,2);
keep country period d p;
run;

/* this is all you need with an intelligent structure */
data want_long;
set have_long;
if d ne .;
run;

Re: Merging by row

Epid — Thu, 20 Feb 2020 21:35:57 GMT

Thank you very much KurtBremser.

Re: Merging by row

Epid — Thu, 20 Feb 2020 21:40:46 GMT

Thank you very much all. All your answers were very helpful.