How to summarize with new variable names?

[wernie]

I have data that I summarized to get cases by year and tract. The example looks like this:

Cases      Year      CT

2              2010       020300

1             2010        020400

2              2011       020500

1             2012        020300

I want to transform the data so I end up with something like this:

CT             Case2010        Case2011          Case2012

020300       2                            0                        1

020400       1                            0                        0

020500       0                             2                       0

How do I do this?? I'm really stuck. Thanks!

[PaigeMiller]

PROC TRANSPOSE will do this for you, you will need to use BY CT;

Usually, this type of transformation into a format where years become part of the variable names is not useful in SAS (although there are exceptions, one of which is if you need to report this in Excel in a pre-determined format)

[wernie]

Thanks! It's showing COL1 and COL2 for the new columns, which makes me think it's not doing what I want it to be doing because there should be 3 variables for the different years.
I did proc transpose data = in out = new;
by CT;
run;

I think I'm missing something?

[PeterClemmensen · Accepted solution]

data have;
input Cases Year CT;
datalines;
2 2010 020300
1 2010 020400
2 2011 020500
1 2012 020300
;

proc sort data=have;
	by CT;
run;

proc transpose data=have out=want(drop=_name_) prefix=Case;
	by CT;
	id Year;
	var Cases;
run;

[novinosrin]

Hi @wernie 

Not really recommending, but that's how l learned/learn.

data have;
input Cases      Year      CT;
cards;
2              2010       020300
1             2010        020400
2              2011       020500
1             2012        020300
;

proc sql noprint;
select distinct cats('Cases_',year),min(year),max(year) into :var_names separated by ' ',:min trimmed,:max trimmed
from have;
quit;

%let l=%eval(&max-&min+1);

proc sort data=have out=_have;
by ct ;
run;
data want;
 if 0 then set _have;/*maintain the order of variables*/
 array C(&min:&max) &var_names (&l*0);
 retain k;
 if _n_=1 then k=peekclong(addrlong(c(&min)),&l*8);
 else  call pokelong(k,addrlong(c(&min)),&l*8);
  do until(last.ct);
   set _have;
   by ct;
   c(year)=cases;
  end;
keep ct Cases_:;
run;

[novinosrin]

Hello @wernie   This meets your need too

data have;
input Cases      Year      CT;
cards;
2              2010       020300
1             2010        020400
2              2011       020500
1             2012        020300
;

proc freq data=have noprint;
weight cases;
tables ct*year/sparse out=temp(drop= percent);
run;

proc transpose data=temp out=want(drop=_:) prefix=Case_;
by ct;
id year;
var count;
run;

Notice there is no sort required. 🙂