Hello everyone,
I’m trying to get the percentages of Missing, Grade1, Grade2, Grade3 columns. I should bring the Variable column values as unique so I used GROUP BY option; I also need to bring ID column as singular that’s why I used DISTINCT option. Actually, I need to calculate percentages of Missing, Grade1, Grade2, Grade3 columns by grouping Variable column and getting ID column as Distinct. While I can get the Variable column as unique in Want dataset, I can’t get the Variable column as unique when I try to use Subquery in Want2 dataset. Please find my desired output below. Could you help me or show me other methods to get my desired output, please ?
DATA Have;
Length Variable $ 20 ID $ 10 Missing 8 Grade1 8 Grade2 8 Grade3 8;
Infile Datalines Missover ;
Input Variable ID Missing Grade1 Grade2 Grade3;
Datalines;
Variable1 0001 0 0 1 0
Variable1 0001 0 1 0 0
Variable1 0002 0 0 0 1
Variable1 0002 0 0 1 0
Variable1 0003 0 1 0 0
Variable1 0003 0 0 0 1
Variable2 0001 0 1 0 0
Variable2 0001 0 1 0 0
Variable2 0002 0 0 1 0
Variable2 0002 0 0 0 1
Variable2 0003 0 0 0 1
Variable2 0003 0 0 1 0
Variable3 0001 0 1 0 0
Variable3 0001 0 1 0 0
Variable3 0002 0 0 1 0
Variable3 0002 0 0 0 1
Variable3 0003 0 0 1 0
Variable3 0003 0 0 0 1
;
Run;
PROC SQL;
CREATE TABLE Want AS
SELECT Variable
,(SUM(Grade1)) AS Count1
/*,(SUM(Grade1)/100) FORMAT=percent7.1 as Percent1*/
,(SUM(Grade2)) AS Count2
/*,(SUM(Grade2)/100) FORMAT=percent7.1 as Percent2*/
,(SUM(Grade3)) AS Count3
/*,(SUM(Grade3)/100) FORMAT=percent7.1 as Percent3*/
,(SUM(Missing)) AS CountM
/*,(SUM(Missing)/100) FORMAT=percent7.1 as PercentM*/
,(COUNT(DISTINCT(ID))) AS CountT
/*,(COUNT(DISTINCT(ID))/100) FORMAT=percent7.1 as PercentT*/
FROM Have
GROUP BY Variable;
QUIT;
PROC SQL;
Create Table Want2 As
Select
Counts.*
/*H1.Variable
,Counts.Count1
,Counts.Count1/SUM(Counts.Count1) FORMAT=percent7.1 as Percent1
,Counts.Count2
,Counts.Count2/SUM(Counts.Count2) FORMAT=percent7.1 as Percent2
,Counts.Count3
,Counts.Count3/SUM(Counts.Count3) FORMAT=percent7.1 as Percent3
,Counts.CountM
,Counts.CountM/SUM(Counts.CountM) FORMAT=percent7.1 as PercentM
,Counts.CountT
,Counts.CountT/SUM(Counts.CountT) FORMAT=percent7.1 as PercentT*/
From Have H1,
(Select Variable
,SUM(Grade1) AS Count1
,SUM(Grade2) AS Count2
,SUM(Grade3) AS Count3
,SUM(Missing) AS CountM
,COUNT(DISTINCT(ID)) AS CountT
From Have
GROUP BY Variable) Counts;
QUIT;
Thank you.
... View more
