topic Re: Grouping by character variable in SAS Data Management

Grouping by character variable

Ujjawal — Fri, 01 Aug 2014 09:28:24 GMT

I have created the following dataset for reference :

data readin;

Input X$ Y;

cards;

D 1

B 2

A 2

A 3

B 3

A 3;

run;

I want to calculate count of X by Y variable. Since X is a character variable, the aggregation cannot be done using PROC MEANS and PROC TABULATE. I dont want to create X into numerical groups. I know this can be easily done via PROC SQL (Code shown below)

PROC SQL;

Create table readin1 as

Select Y, count(X) from

readin

group by Y;

quit;

Is it possible via data step or procedures other than PROC SQL?

Re: Grouping by character variable

RW9 — Fri, 01 Aug 2014 09:46:30 GMT

Well, first question would be that SQL does the job pretty well, why so reluctant to not use it? I suppose you could do it by creating a format, and then putting your character into that format so the underlying data would be numeric, and displayed as A or B. Then means would work. You could also use rank procedure to derive a value for each one:

data readin;

Input X$ Y;

cards;

D 1

B 2

A 2

A 3

B 3

A 3

;

run;

data want;

set readin;

numversion=rank(x);

run;

Finally way I can think of is to retain the last value each time:

proc sort data=readin;

by y x;

run;

data want;

set readin;

retain count;

by Y X;

if first.X then count=1;

else count=count+1;

if last.X then output;

run;

Re: Grouping by character variable

Kurt_Bremser — Fri, 01 Aug 2014 10:31:17 GMT

You could use proc means without a variable and take the _freq_ output value.

proc sort data=readin;

by y x;

run;

proc means data=readin;

by y x;

output out=want;

run;

Re: Grouping by character variable

data_null__ — Fri, 01 Aug 2014 11:47:49 GMT

Your PROC SQL is counting the non-missing values of X grouped by Y. That can also be done with PROC SUMMARY and a WHERE statement.

data readin;
Input X$ Y;
cards; 
D 1
B 2
A 2
A 3
A 3
. 3
B 3
A 3
; 
run; 

PROC SQL; 
   Select Y, count(X) from readin
   group by Y;
   quit; 
   run; 
proc summary nway; 
   where not missing(x);
   class y;
   output out=freq;
   run; 
proc print; 
   run;

Re: Grouping by character variable

ballardw — Fri, 01 Aug 2014 16:30:33 GMT

I would be interested in seeing the PROC Tabulate that didn't work as I count character variables often using Tabulate.

Re: Grouping by character variable

Ujjawal — Fri, 01 Aug 2014 19:19:57 GMT

@ballardw : Thank you for your email. The following PROC TABULATE code doesn't work for me as X is a character variable.

PROC TABULATE DATA = READIN;

CLASS Y;

VAR X;

TABLE X*N*Y;

RUN;

Re: Grouping by character variable

ballardw — Fri, 01 Aug 2014 19:24:20 GMT

Var variables in tabulate are expected to be numeric. Make X a class variable and it should work.

Re: Grouping by character variable

Ujjawal — Fri, 01 Aug 2014 19:32:04 GMT

If i make X a class variable, it will return cross tab. This is what i am not looking for. I want count of X grouped by Y.

Refer the code : -

PROC SQL;

Create table readin1 as

Select Y, count(X) from

readin

group by Y;

quit;

Re: Grouping by character variable

data_null__ — Fri, 01 Aug 2014 19:37:08 GMT

proc tabulate data=readin;
   where not missing(x);
   class y;
   tables y;
   run;

Re: Grouping by character variable

stat_sas — Fri, 01 Aug 2014 19:52:28 GMT

proc tabulate data=readin out=want(where=(x = ' ') drop=_:);

class x y;

table y,x all;

run;

proc print data=want;

run;