topic collapse data in SAS Programming

collapse data

QLi — Thu, 23 Jun 2011 21:16:26 GMT

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

collapse data

Patrick — Fri, 24 Jun 2011 00:57:25 GMT

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

collapse data

ballardw — Fri, 24 Jun 2011 14:39:50 GMT

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.

collapse data

DLing — Fri, 24 Jun 2011 16:38:40 GMT

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.

collapse data

Peter_C — Fri, 24 Jun 2011 17:25:47 GMT

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

and probably alternatives are suitable candidates, too

hth

peterC

collapse data

QLi — Fri, 24 Jun 2011 18:32:05 GMT

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

collapse data

Ankitsas — Sun, 26 Jun 2011 17:48:04 GMT

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;