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;
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.