sql summary using by

[jkf91]

hi all,

My (wrong) code is as follows:

data cig.years_count2;

  set cig.years_count;

  by household_id, household_size;

  if last.household_id then output;

run;

The data (cig.years_count) looks like this:

household_id	HOUSEHOLD_SIZE	year	numyears
2000351	1	2005	4
2000351	1	2006	4
2000351	1	2007	4
2000351	1	2008	4
2000351	2	2004	1
2000368	2	2008	1
2000394	2	2004	1
2000571	1	2004	5
2000571	1	2005	5
2000571	1	2006	5
2000571	1	2007	5
2000571	1	2008	5

I am not sure why this is not working (I only get household_id, year and numyears), but I want to get:

household_id	HOUSEHOLD_SIZE	numyears
2000351	1	4
2000351	2	1
2000368	2	1
2000394	2	1
2000571	1	5

Basically, I want to get rid of year and get house hold_size.

Can someone please tell me how I can do this?

Thanks,

C

[ari14]

You can use the drop statement as follows:

data cig.years_count2 (drop=numyears);

  set cig.years_count;

  by household_id, household_size;

  if last.household_id then output;

run;

[jkf91]

no that doesn't do the job. that code gives me numyears = 5 for household_id=2000351. I want to see 4 and 1 as in the second table.

also, i want to keep household_size and drop year. the code you provided do the opposite.

[Ksharp · Accepted solution]

Your code should like:

data cig.years_count2;

  set cig.years_count;

  by household_id   household_size;

  if   last.household_size   then output;

run;

Ksharp