06-27-2014 02:38 AM
Suppose I have used PROC SQL to find the number of individuals each year, in the time period 2000 - 2013, having an income .above 100000, like this:
create table nr_of_ind as
select year, count(ind) as noi
group by year
having income > 100000;
If the number of individuals is zero any year, PROC SQL doesn't give any record with thar year and the number 0.
What's the best way to add records with YEAR data, and NOI = 0 to the table nr_of_ind, in such cases?
06-27-2014 02:55 AM
proc sort data=my_table;
data nr_of_ind (keep=year noi);
if first.year then noi = 0;
if income > 100000 then noi + 1;
if last.year then output;
This would only fail to give you an output if you have no individual entry for a given year at all.
Another option would be to create a table with only "year" from 2000 to 2013, and merge/join that back with your initial result and set noi to zero if no record in my_table is present.
06-30-2014 08:58 AM
Thanks for your answers.
Kurt Bremser, you wrote: "Another option would be to create a table with only "year" from 2000 to 2013, and merge/join that back with your initial result and set noi to zero if no record in my_table is present."
How could I combine a join with an if, in proc sql?
06-30-2014 09:05 AM
You do it in a data step.
do year = 2000 to 2013;
if not in_noi then noi = 0;
06-27-2014 04:09 AM
You could create a template dataset as KurtBremser has stated. Its probably easy enough. Alternatively you could think about it this way, create and empty results table, then using data _null_ with a do loop, generate insert statements for each year:
create table WANT
do I=0 to 13;
call execute('proc sql;
insert into WANT
NOI=(select count(IND) from HAVE where YEAR='||strip(put(2000+i,best.))||' and INCOME > 10000);