topic Re: Make a new table from an existing table in a DATA step. in SAS Programming

Make a new table from an existing table in a DATA step.

deleted_user — Thu, 26 Feb 2009 11:13:52 GMT

I have a SAS-table, Population1, that looks like this:

Nation Gender Number
Germany Men 41000000
Germany Women 42000000
France Men 31000000
France Women 31500000

I would like to make a new SAS-table, Population2, based on Population1. It should look like this:

Nation Population Men Women
Germany 83000000 41000000 42000000
France 61500000 31000000 315000000

Shouldn't it be possible to use the DATA step, starting like this:

DATA Population2;
set Population1;
.
.

?

But then I would have to get rid of one row for each nation. I have some problems with that. Any better way?

Susan

Re: Make a new table from an existing table in a DATA step.

FredrikE — Thu, 26 Feb 2009 12:19:22 GMT

Try this:

proc sort data = population1;
by nation;
run;

proc transpose data = population1 out = population2(drop=_name_);
by nation;
id gender;
run;

data population2;
retain nation population men women;
set population2;
length population 8;
population = men + women;
run;

//Fredrik

Re: Make a new table from an existing table in a DATA step.

Patrick — Thu, 26 Feb 2009 13:26:52 GMT

May be you use:
population = sum(men,women);

reason:
a+b: if a or b is Missing the result will be Missing
sum(a,b): Missings values are ignored, sum(a,) resolves to value of a

Re: Make a new table from an existing table in a DATA step.

deleted_user — Thu, 26 Feb 2009 13:57:02 GMT

Thanks!

But if I woluld like to have the nations sorted, in descending order, by the new variable Population, with one exception: the number of my own countrys population should be placed last, with no regard to if that number would be greater than some of the numbers above.

Do you have any idea?

Re: Make a new table from an existing table in a DATA step.

FredrikE — Thu, 26 Feb 2009 15:58:56 GMT

sum() is better of course.

Rank your records, can be done using a new variable (rank) that gets the value of population for every record except your own country record which get population = -1

Sort data by rank...

!?