topic combining columns and renaming them in SAS Procedures

combining columns and renaming them

ahappel — Thu, 03 Feb 2011 17:46:48 GMT

Hello,
I know this is simple but I'm a new grad student and trying to get this to work.

I have data formatted like this:
LOC FISHID PREY AMT
1 1 1 3
1 1 2 30
1 1 3 9
1 1 4 12
1 2 4 6
1 2 6 1

I would like to combine the totals for things like prey items 2 and 3 into one total named something like "worms". And then say name prey items 4 through 11 "fish" and so on.
1 1 beetles 3
1 1 Worms 39
1 1 fish 3
1 2 fish 7

The format for the names would need to be kept as later in the coding i have a proc transform to get a single line per fish with prey turned into columns.
EX:
Loc Fishid Beetles Worms Fish

So How do do the first transformation so that the second one works?
Thanks so much for any help.

Re: combining columns and renaming them

sbb — Thu, 03 Feb 2011 18:25:25 GMT

Look at using a combination of MERGE with a BY statement and also PROC TRANSPOSE with ID and IDLABEL.

Scott Barry
SBBWorks, Inc.

Re: combining columns and renaming them

deleted_user — Fri, 04 Feb 2011 09:20:29 GMT

hello,

if i understood correctly you can use proc format to "label" the prey variable
and then the means procedure to get the output data set:

[pre]

DATA A;
INPUT LOC FISHID PREY AMT;
datalines;
1 1 1 3
1 1 2 30
1 1 3 9
1 1 4 12
1 2 4 6
1 2 6 1
;

proc format;
value name
1='beetles'
2-3='Worms'
4-11='fish';
run;

proc means data=a noprint nway;
class loc fishid prey;
var amt;
output out=a1 (drop=_type_) sum=;
format prey name.;
run;

[/pre]

Marius