DATA Step, Macro, Functions and more

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

Reply
Contributor
Posts: 30

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

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?

Super User
Posts: 6,963

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 30

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

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?

Super User
Posts: 6,963

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

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;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,413

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

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;

Respected Advisor
Posts: 3,777

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

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

Super User
Posts: 9,687

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

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

Ask a Question
Discussion stats
  • 6 replies
  • 337 views
  • 0 likes
  • 5 in conversation