collapse data

[QLi]

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

Thanks in advance

[Patrick]

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

[ballardw]

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.

[QLi]

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

[DLing · Accepted solution]

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.

[Peter_C]

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

and probably alternatives are suitable candidates, too

hth

peterC

[Ankitsas]

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;