BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
QLi
Fluorite | Level 6 QLi
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
DLing
Obsidian | Level 7

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.

View solution in original post

6 REPLIES 6
Patrick
Opal | Level 21

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
Super User

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
Fluorite | Level 6 QLi
Fluorite | Level 6

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

DLing
Obsidian | Level 7

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
Rhodochrosite | Level 12

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

and probably alternatives are suitable candidates, too

 

hth

peterC

Ankitsas
Calcite | Level 5

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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