SAS freq multiple tables append into one table

[bikashten]

Hi, 

I have this table from SAS freq output. I have 100 variables.

Obs	var1	COUNT	PERCENT
1	.	2	.
2	-1	1	25
3	0	1	25
4	1	2	50
The desired output I want is
var	.	-1	0	1
var1	2	1	1	2

I do not want to go one by one variable to create the desired output. I am wondering about any macros or something that create the desired table.

Thank you

Bikash

[Kurt_Bremser]

What should be the names of the columns?

[bikashten]

Hi Kurt, 

The column names are the categories within each variable. The column name is same for all the variables: ., -1, 0, 1. 

Thanks,

Bikash

[Kurt_Bremser]

---

@bikashten wrote:

Hi Kurt, 

The column names are the categories within each variable. The column name is same for all the variables: ., -1, 0, 1. 

Thanks,

Bikash

---

Not possible. See Names in the SAS Language.

[ballardw]

---

@bikashten wrote:

Hi Kurt, 

The column names are the categories within each variable. The column name is same for all the variables: ., -1, 0, 1. 

Thanks,

Bikash

---

That is likely to be an horrifically ugly table with hundreds of columns and few rows.

I would suggest that Proc tabulate might be a reasonable choice:

proc tabulate data=sashelp.class;
   class sex age ;
   tables sex age,
          n='Count' colpctn='%'
          ;
run;

or

proc tabulate data=sashelp.class;
   class sex age ;
   tables  n='Count' rowpctn='%',
           sex age
          ;
run;

For an example with two variables.

If your variables include things that are really continuous such as the height and weight in SASHELP.Class you would have to work very hard to convince me that putting all of that as single values is adding much to what ever you may be attempting to present.

Note that proc tabulate will create an output data set but it can be quite ugly

[RW9]

Please post test data in the form of a datastep:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

Then show what you want out from that data, looks like a simple proc transpose of count, id var1 with a prefix as the names are not valid SAS names.

[bikashten]

HI RW9,

Here is a sample data set. I have >100 variables, but I am interested only total count of positive, negative, 0 and missing values for each numeric variable. 

data test;
input var1 var2 var3 var4 $ var5 $;
datalines;
1 2 3 a d
1 4 1 b d
-2 3 -1 c d
. -1 2 d d
. . . . d
0 0 0 ed d
;
/*create the new coding based on the values */;

data new;
set test;
array Nums{*} _numeric_;
do i=1 to dim(Nums);
if Nums[i]=. then Nums[i]=.;
else if Nums[i]<0 then Nums[i]=-1;
else if Nums[i]=0 then Nums[i]=0;
else if Nums[i]>0 then Nums[i]=1;
end;
run;

/* proc contents for data set */;

proc contents data=new varnum out=test2 (keep=name Type);
run;

proc sql;
create table OutputData_vars_num as
select Name
from test2
where type=1 and Name not in ("i");
quit;

proc print data=OutputData_vars_num;
run;

options symbolgen mlogic mprint;
Proc sql;
Select strip(put (Count(name), 3.)) into :varcount_num
from OutputData_vars_num;
quit;

/* create macro for numeric variables */;

Proc sql;
Select Name
into :y1 - :y&varcount_num
from OutputData_vars_num;
quit;

/* do loop create each freq tables */;

%macro summary;
%do i=1 %to &varcount_num;
proc freq data=new;
tables &&y&i/list out=out&&y&i;
run;
%end;
%mend;
%summary

Now a big thing is to append the tables from all the freq tables, and please, let me know if you have any ideas about it. Proc tabulate is good for few variables, it would be ugly if the data set has tons of variables.

Thanks,

Bikash

[RW9]

Am now on holiday, so will only breifly respond to this.

If you ever find yourself doing:

%macro summary;
%do i=1 %to &varcount_num;
proc freq data=new;
tables &&y&i/list out=out&&y&i;

This type of coding, you are doing it wrong.  Simple as that.  Remodel your data so that you have all the data in one dataset, with variables which indicate the different levels - e.g. a variable which canrepresent the different tables you create from this loop.  Then put in your code:

proc freq data=new;
  by thevariableyoucreate;
  tables list out=want;
run;

This is the way to handle by group processing, not by doing each one separately and then patching them all back which both creates messsy unmaintainable code, and takes far longer to process and takes more resource.

Simple tip:

If you have do loops in macro or more than one & you are modelling your data wrong and are trying to patch it up in coding.

[Tom]

If you have a lot of variables that you want to treat the same and summarize by variable name then consider transposing the data.

proc transpose data=have out=tall ;
run;
proc format ;
  value report low-0='<0' 0='Zero' 0-high='>0' other='Missing';
run;
proc freq data=tall;
  tables _name_*col1 ;
  format col1 report.;
run; 

[bikashten]

Hi Tom,

None of them is working right now. Let's me clear one thing: I have >100 discrete variables and class within each variable is -1, 0, 1. This is just a sample data set. 

data test;
input var1 var2 var3;
datalines;
1 1 -1
0 0 0
-1 -1 -1
1 1 0
0 1 1
;

proc freq data=test;
tables var1 var2 var3;
run;

The FREQ Procedure

var1 Frequency Percent CumulativeFrequency CumulativePercent-101

1	20.00	1	20.00
2	40.00	3	60.00
2	40.00	5	100.00

var2 Frequency Percent CumulativeFrequency CumulativePercent-101

1	20.00	1	20.00
1	20.00	2	40.00
3	60.00	5	100.00

var3 Frequency Percent CumulativeFrequency CumulativePercent-101

2	40.00	2	40.00
2	40.00	4	80.00
1	20.00	5	100.00

I would like to combine these three SAS freq outputs into one table like this:

variable	-1	0	1
var1	1	2	2
var2	1	1	3
var3	2	2	1

It looks pretty simple to append these three tables, but I have a hard time to figure it out.

Thanks,

Bikash

[bikashten]

I don't know somehow variable class is missing in the above sas freq output. The class within each var is -1, 0, 1.

Thanks,

Bikash

[Tom]

Did you try transposing your data into a tall table with name/value pairs?  You need to add an unique row id variable to use PROC TRANSPOSE.

[bikashten]

Yes, I tried it. I added the id in to the data set and use proc transpose.
proc transpose data=test out=want;
id id;
run;

proc freq data=want;
tables _NAME_*_1;
run;

It gives the count by var for each column, but I need the count from all columns together for each var from proc transpose data.

Thanks,
Bikash

[Tom]

You need to BY statement, not an ID statement. That will tell TRANPOSE to transpose each row into a single column instead of treating the whole dataset as if it was a matrix (converting number of rows into number of columns).

[Kurt_Bremser · Accepted solution]

Transpose, Freq, Transpose.

See this:

data test;
input var1 var2 var3;
datalines;
1 1 -1
0 0 0
-1 -1 -1
1 1 0
0 1 1
;
run;

data pretrans; /* create a virtual "ID" for transpose to work */
set test;
number = _n_;
run;

proc transpose data=pretrans out=trans;
by number;
var var:;
run;

proc sort data=trans;
by _name_;
run;

proc freq data=trans noprint;
by _name_;
tables col1 / out=freq;
run;

proc transpose
  data=freq
  out=want (
    drop=_label_
    rename=(_name_=variable)
  )
;
by _name_;
id col1;
var count;
run;

proc print data=want noobs;
run;

Result:

variable    -1    0    1

  var1       1    2    2
  var2       1    1    3
  var3       2    2    1

Note that

options validvarname=any;

has to be in effect, otherwise the output would be

variable    N1    _0    _1

  var1       1     2     2
  var2       1     1     3
  var3       2     2     1