Merging by row

[Epid]

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

[ed_sas_member]

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;

[PeterClemmensen · Accepted solution]

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;

[Kurt_Bremser]

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;

[Epid]

Thank you very much KurtBremser.

[Epid]

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