I have a question regarding adding rows in between another rows. (maybe using do loops?)
I have a data for each quater for each firm. quarters without observations are removed. How can I create rows with zero for those quarters without observations?
For simplicity, let's say there are three firms, for year 2000 and 2001. And I have a data like this:
data table1;
input firm_id year quarter value;
cards;
0001 2000 1 10
0001 2000 3 20
0001 2001 2 45
0001 2001 3 14
0001 2001 4 45
0012 2000 1 34
0012 2000 2 10
0047 2000 1 34
0047 2001 3 34
;
run;
And eventually I want a data looks like
Thanks!
MIssing years as well as missing quarters? If you know you want a fixed range of years for every ID, then it's relatively straightforward:
data want (drop=qtr_found:);
array qtr_found {2000:2001,4} ;
/* Read one ID to establish qtr_found flags */
do until (last.id);
set table1 (keep=id year quarter);
by id ;
qtr_found{year,quarter}=1;
end;
/* Loop over the flags to control re-reading and output */
do year=lbound1(qtr_found) to hbound1(qtr_found);
do quarter=1 to 4;
if qtr_found{year,quarter} then set table1;
else value=0;
output;
end;
end;
run;
Notes:
This can be done by reading each year of data twice. The first read establishes 4 flags, 1 for each quarter, indicating whether that quarter is present in the given year. The second read goes through the 4 flags. If the flag is 1, then read a single record, if not then set value to 0. Then output the record.
data want (drop=qtr_found:);
array qtr_found {4} ;
do until (last.year);
set table1 (keep=id year quarter);
by id year;
qtr_found{quarter}=1;
end;
do quarter=1 to 4;
if qtr_found{quarter} then set table1;
else value=0;
output;
end;
run;
Below picture is the result based on your code.
But for firm_id=12, it has results only for year 2000. I guess it is because in the original data, there was no observation at all for firm 12 in year 2001. But I need 4 rows all with zero for firm 12 for year 2001. How can I fix this problem?
Thanks again
I like @mkeintz's approach, but it didn't account for the missing year. I think the following will:
data table1; set table1; by firm_id year; if last.firm_id and year eq 2000 then do; output; year=2001; quarter=1; value=0; output; end; else output; run; data want (drop=qtr_found:); array qtr_found {4} ; do until (last.year); set table1 (keep=firm_id year quarter); by firm_id year; qtr_found{quarter}=1; end; do quarter=1 to 4; if qtr_found{quarter} then set table1; else value=0; output; end; run;
Art, CEO, AnalystFinder.com
MIssing years as well as missing quarters? If you know you want a fixed range of years for every ID, then it's relatively straightforward:
data want (drop=qtr_found:);
array qtr_found {2000:2001,4} ;
/* Read one ID to establish qtr_found flags */
do until (last.id);
set table1 (keep=id year quarter);
by id ;
qtr_found{year,quarter}=1;
end;
/* Loop over the flags to control re-reading and output */
do year=lbound1(qtr_found) to hbound1(qtr_found);
do quarter=1 to 4;
if qtr_found{year,quarter} then set table1;
else value=0;
output;
end;
end;
run;
Notes:
data table1;
input firm_id year quarter value;
cards;
0001 2000 1 10
0001 2000 3 20
0001 2001 2 45
0001 2001 3 14
0001 2001 4 45
0012 2000 1 34
0012 2000 2 10
0047 2000 1 34
0047 2001 3 34
;
run;
proc sql;
create table want as
select a.*,coalesce(b.value,0) as value
from
(select * from (select distinct firm_id from table1),
(select distinct year from table1),
(select distinct quarter from table1) ) as a
left join table1 as b
on a.firm_id=b.firm_id and a.year=b.year and a.quarter=b.quarter;
quit;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.