Solved
Contributor
Posts: 59

# collapse data

Hi,

I have this kind data:

Full_Name      City        State    H_RCl    P_RC1   S_RC1    T_R1

Rice Brown      Raleigh    NC        \$167      0             0             0

Rice Brown     Raleigh    NC         0          0               \$123          0

Rice Brown        Raleigh    NC         0          124            0             0

Rice Brwon      Raleihg     NC         0           0            0           \$125

I want get this one row data:

Full_Name City    State    h_RC1    P_RC1    S_RC1    T_RC1

Rice Brown  Raleigh NC   \$167      \$124        \$123           \$125

Accepted Solutions
Solution
‎06-24-2011 12:38 PM
Frequent Contributor
Posts: 104

## collapse data

proc means data=have missing nway;

class Full_Name City State;

var H_RCl P_RC1 S_RC1 T_R1;

output sum= out=want (drop=_;

run;

nway generates only the lowest level summary rows.

missing is defensive practice in case your data have missing or blank values in the CLASS variables.

All Replies
Posts: 4,736

## collapse data

proc means data=have noprint;

by Full_Name City State;

var H_RCl P_RC1 S_RC1 T_R1;

output sum= out=want (drop=_;

run;

HTH

Patrick

Super User
Posts: 13,583

## collapse data

Or

proc means data=have noprint nway;

class Full_Name City State;

var H_RCl P_RC1 S_RC1 T_R1;

output sum= out=want (drop=_;

run;

Use of BY requires sorting data first, CLASS does not. NWAY restricts output to the full combination of CLASS variables.

Contributor
Posts: 59

## collapse data

According these suggestions, I worked it out. Thanks all.

Solution
‎06-24-2011 12:38 PM
Frequent Contributor
Posts: 104

## collapse data

proc means data=have missing nway;

class Full_Name City State;

var H_RCl P_RC1 S_RC1 T_R1;

output sum= out=want (drop=_;

run;

nway generates only the lowest level summary rows.

missing is defensive practice in case your data have missing or blank values in the CLASS variables.

Valued Guide
Posts: 2,191

## collapse data

What kind of "collapse"? SUM, MAX, MEAN, latest

and probably alternatives are suitable candidates, too

hth

peterC

Occasional Contributor
Posts: 14

## collapse data

Friend,

Please find the below code , this will you the output as per your needs:-

data test;

input @1 full_name \$10.  @12 city \$7. @20 state \$  @23 h_rc1 comma5. @28 p_rc1 comma5. @32 s_rc1 comma5. @37 t_rc1 comma5.;

datalines;

Rice Brown Raleigh NC \$167 0   0    0

Rice Brown Raleigh NC 0    0   \$123 0

Rice Brown Raleigh NC 0    124 0    0

Rice Brown Raleigh NC 0    0   0    \$125

;

data test1;

set test;

by full_name city state;

if first.state then s=0;

if first.state then s1=0;

if first.state then s2=0;

if first.state then s3=0;

s+h_rc1;

s1+p_rc1;s2+s_rc1;s3+t_rc1;

if last.state;

h_rc1=s;

p_rc1=s1;

s_rc1=s2;

t_rc1=s3;

run;

🔒 This topic is solved and locked.