DATA Step, Macro, Functions and more

collapse data

Accepted Solution Solved
Reply
Contributor QLi
Contributor
Posts: 59
Accepted Solution

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

Thanks in advance


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=_Smiley Happy;

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.

View solution in original post


All Replies
Respected Advisor
Posts: 4,173

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=_Smiley Happy;

run;

HTH

Patrick

Super User
Posts: 11,343

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=_Smiley Happy;

run;

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

Contributor QLi
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=_Smiley Happy;

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,177

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 861 views
  • 3 likes
  • 6 in conversation