Hello everyone,
I would like to ask my question to you. Again, it includes to get the data set output of some procedures. I know, SAS doesn’t let us to get the data set which has alredy had same structure with Report output but I need to create my desired output with some other methods.
I have a sample code as below(My real data is too big). Proc Tabulate almost can provide my desired output but I need some additional columns on my desired output.
Sample Data Set;
Data Have;
Length Variable1 8 RankVariable 8;
Infile Datalines Missover;
Input Variable1 RankVariable;
Datalines;
0 0
1 2
0 0
1 0
0 2
1 0
1 2
1 2
0 2
;
Run;
Here is my desired output;
My first problem is to show values as zero even they don’t exist in the data set. For Example, in my sample data set there is no “1” values for "RankVariable" so “PROC TABULATE” doesn’t bring them in its report output, I also need this values as “0”.
Secondly, I need to add cumulative column but this column needs to show the values being percentage values.
Current Proc Tabulate Statement;
Proc Tabulate Data=Have Out=Have2;
Class RankVariable / Order=Unformatted Missing;
Class Variable1 / Order=Unformatted Missing;
Table RankVariable , Variable1;
Run;
I also need this report output as data set. It shouldn’t be same structure, I need the following data set.
Data Set Desired;
Actually, this following steps could help me to create the data set but it is too manual, I think there should be more simple method to do the foreigoing demands. At this point, I would like to get your helps.
Proc Sql;
Create table ZeroCount AS
Select
Count(Case When Variable1=0 And RankVariable=0 Then 0 Else . End) AS N0
,Count(Case When Variable1=0 And RankVariable=1 Then 1 Else . End) AS N1
,Count(Case When Variable1=0 And RankVariable=2 Then 2 Else . End) AS N2
From Have;
Create Table OneCount AS
Select
Count(Case When Variable1=1 And RankVariable=0 Then 0 Else . End) AS N0
,Count(Case When Variable1=1 And RankVariable=1 Then 1 Else . End) AS N1
,Count(Case When Variable1=1 And RankVariable=2 Then 2 Else . End) AS N2
From Have;
Quit;
PROC SQL;
CREATE TABLE CountTable AS
SELECT * FROM ZEROCOUNT
OUTER UNION CORR
SELECT * FROM ONECOUNT;
Quit;
PROC TRANSPOSE DATA=CountTable
OUT=CountTable2
PREFIX=Col
NAME=Count;
VAR N0 N1 N2;
RUN;
QUIT;
Data Cumulative;
Set CountTable2;
CumulativeColumn + Col2;
Run;
Thank you
... View more
