topic Re: How to summarize with new variable names? in SAS Programming

How to summarize with new variable names?

wernie — Wed, 14 Aug 2019 17:31:26 GMT

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!

Re: How to summarize with new variable names?

PaigeMiller — Wed, 14 Aug 2019 17:45:14 GMT

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)

Re: How to summarize with new variable names?

wernie — Wed, 14 Aug 2019 17:54:50 GMT

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?

Re: How to summarize with new variable names?

PeterClemmensen — Wed, 14 Aug 2019 18:12:25 GMT

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;

Re: How to summarize with new variable names?

novinosrin — Wed, 14 Aug 2019 18:42:26 GMT

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;

Re: How to summarize with new variable names?

novinosrin — Wed, 14 Aug 2019 19:12:09 GMT

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. 🙂