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
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;
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;
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;
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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.