topic Re: Adding records when PROC SQL doesn't give any count record = 0. in SAS Programming

Adding records when PROC SQL doesn't give any count record = 0.

attjooo — Fri, 27 Jun 2014 06:38:54 GMT

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:

proc sql

create table nr_of_ind as

select year, count(ind) as noi

from my_table

group by year

having income > 100000;

quit;

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?

Re: Adding records when PROC SQL doesn't give any count record = 0.

Kurt_Bremser — Fri, 27 Jun 2014 06:55:32 GMT

proc sort data=my_table;

by year;

run;

data nr_of_ind (keep=year noi);

set my_table;

by year;

retain noi;

if first.year then noi = 0;

if income > 100000 then noi + 1;

if last.year then output;

run;

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.

Re: Adding records when PROC SQL doesn't give any count record = 0.

RW9 — Fri, 27 Jun 2014 08:09:29 GMT

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:

proc sql;

create table WANT

(

YEAR num,

NOI num

);

quit;

data _null_;

do I=0 to 13;

call execute('proc sql;

insert into WANT

set YEAR='||strip(put(2000+i,best.))||',

NOI=(select count(IND) from HAVE where YEAR='||strip(put(2000+i,best.))||' and INCOME > 10000);

quit;');

end;

run;

Re: Adding records when PROC SQL doesn't give any count record = 0.

data_null__ — Fri, 27 Jun 2014 10:38:27 GMT

The "best" way to do this is to use the features of PROC SUMMARY COMPLETETYPES and PRELOADFMT or CLASSDATA.

Re: Adding records when PROC SQL doesn't give any count record = 0.

Ksharp — Fri, 27 Jun 2014 12:25:56 GMT

Does these year have already been involved into your data ? What does your sample data look like ?

Re: Adding records when PROC SQL doesn't give any count record = 0.

attjooo — Mon, 30 Jun 2014 12:58:30 GMT

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?

Re: Adding records when PROC SQL doesn't give any count record = 0.

Kurt_Bremser — Mon, 30 Jun 2014 13:05:58 GMT

You do it in a data step.

data lookup;

do year = 2000 to 2013;

output;

end;

run;

data nr_of_ind2;

merge

nr_of_ind (in=in_noi)

lookup

;

by year;

if not in_noi then noi = 0;

run;