BookmarkSubscribeRSS Feed
attjooo
Calcite | Level 5

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?

6 REPLIES 6
Kurt_Bremser
Super User

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.

attjooo
Calcite | Level 5

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?

Kurt_Bremser
Super User

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;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

data_null__
Jade | Level 19

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

Ksharp
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1178 views
  • 0 likes
  • 5 in conversation