BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
turcay
Lapis Lazuli | Level 10

Hello everyone, I think I have a very simple question but I can't handle it. I'm trying to get percentage of values of the variables. I think I'm so close the desired output but I want to be sure. Could you help me, please ?

 

Sample Data ;

DATA Have;
Length Variable_ID $ 3 Variable $ 3;
Infile Datalines Missover Dlm=",";
Input Variable_ID Variable;
Datalines;
001,TUR
001,TUR
002,TUR
002,TUR
003,ENG
003,ENG
004,GER
004,GER
005,FRA
005,FRA
;
Run;
PROC SQL;
Create Table Want As
Select
Variable,
Count(1) as CountOfVariable
/*Calculated CVariable/100 as Percent format=percent8.2*/
From Have
group by Variable;
QUIT;

 

Desired Output;

 

Desired.png

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

I am not quit sure what you are trying to do .

 

 

DATA Have;
Length Variable_ID $ 3 Variable $ 3 Number 8;
Infile Datalines Missover Dlm=",";
Input Variable_ID Variable Number;
Datalines;
001,TUR,1
001,TUR,1
002,TUR,0
002,TUR,0
003,ENG,1
003,ENG,1
004,GER,1
004,GER,1
005,FRA,1
005,FRA,1
;
Run;
DATA Have2;
Length Variable $ 3 Number 8;
Infile Datalines Missover Dlm=",";
Input Variable Number;
Datalines;
TUR,1
TUR,1
TUR,0
TUR,0
ENG,1
ENG,1
GER,1
GER,1
FRA,1
FRA,1
;
Run;
PROC SQL;
CREATE TABLE Want AS
SELECT 
H1.Variable,
COUNT(*)/(SELECT count(*)  FROM Have H1 
INNER JOIN Have2 H2
ON H1.Variable=H2.Variable
WHERE H2.Number=1 AND H1.Number=1 ) AS Percent FORMAT=Percent8.2
FROM Have H1 
INNER JOIN Have2 H2
ON H1.Variable=H2.Variable
WHERE H2.Number=1
AND H1.Number=1
group BY H1.Variable;
QUIT;

View solution in original post

12 REPLIES 12
Jagadishkatam
Amethyst | Level 16
proc sql;
select count(*) into: obs from have; 
create table test as select variable, (count(variable)/&obs) as var format=percent8.2, count(*) as count from have group by variable;
quit;
Thanks,
Jag
PaigeMiller
Diamond | Level 26

I feel the solutions provided by @Jagadishkatam and also by @mohamed_zaki could give the wrong result in some situations.

 

If there are missing values in variable, then the variable &obs will count the missings, and then in the next line, (count(variable)/&obs) will have the wrong denominator.

 

--
Paige Miller
mohamed_zaki
Barite | Level 11
PROC SQL noprint;
select nobs into :nobs from dictionary.tables 
where libname="WORK" and memname="HAVE";

Create Table Want As
Select
Variable, Count(1)/&nobs as Percent format=percent8.2
From Have
group by Variable;
QUIT;
Ksharp
Super User
DATA Have;
Length Variable_ID $ 3 Variable $ 3;
Infile Datalines Missover Dlm=",";
Input Variable_ID Variable;
Datalines;
001,TUR
001,TUR
002,TUR
002,TUR
003,ENG
003,ENG
004,GER
004,GER
005,FRA
005,FRA
;
Run;
PROC SQL;
Create Table Want As
Select
Variable,
count(*)/(select count(*) from have) as Percent format=percent8.2
From Have
group by Variable;
QUIT;
turcay
Lapis Lazuli | Level 10

Hello,

 

Thank you all of them,I missed out joining with another table while i did this process according to the following conditions.

 

Conditions

Have1.Number=1
Have2.Number=1

 

DATA Have;
Length Variable_ID $ 3 Variable $ 3 Number 8;
Infile Datalines Missover Dlm=",";
Input Variable_ID Variable Number;
Datalines;
001,TUR,1
001,TUR,1
002,TUR,0
002,TUR,0
003,ENG,1
003,ENG,1
004,GER,1
004,GER,1
005,FRA,1
005,FRA,1
;
Run;
DATA Have2;
Length Variable $ 3 Number 8;
Infile Datalines Missover Dlm=",";
Input Variable Number;
Datalines;
TUR,1
TUR,1
TUR,0
TUR,0
ENG,1
ENG,1
GER,1
GER,1
FRA,1
FRA,1
;
Run;
PROC SQL;
CREATE TABLE Want AS
SELECT
H1.Variable,
COUNT(*)/(SELECT count(*) FROM Have) AS Percent FORMAT=Percent8.2
FROM Have H1
INNER JOIN Have2 H2
ON H1.Variable=H2.Variable
WHERE H2.Number=1
AND H1.Number=1
ORDER BY H1.Variable;
QUIT;

Now my desired output as below, Do you have an idea ?

 

Desired.png

 

Thanks.

 

 

Ksharp
Super User

I am not quit sure what you are trying to do .

 

 

DATA Have;
Length Variable_ID $ 3 Variable $ 3 Number 8;
Infile Datalines Missover Dlm=",";
Input Variable_ID Variable Number;
Datalines;
001,TUR,1
001,TUR,1
002,TUR,0
002,TUR,0
003,ENG,1
003,ENG,1
004,GER,1
004,GER,1
005,FRA,1
005,FRA,1
;
Run;
DATA Have2;
Length Variable $ 3 Number 8;
Infile Datalines Missover Dlm=",";
Input Variable Number;
Datalines;
TUR,1
TUR,1
TUR,0
TUR,0
ENG,1
ENG,1
GER,1
GER,1
FRA,1
FRA,1
;
Run;
PROC SQL;
CREATE TABLE Want AS
SELECT 
H1.Variable,
COUNT(*)/(SELECT count(*)  FROM Have H1 
INNER JOIN Have2 H2
ON H1.Variable=H2.Variable
WHERE H2.Number=1 AND H1.Number=1 ) AS Percent FORMAT=Percent8.2
FROM Have H1 
INNER JOIN Have2 H2
ON H1.Variable=H2.Variable
WHERE H2.Number=1
AND H1.Number=1
group BY H1.Variable;
QUIT;
RW9
Diamond | Level 26 RW9
Diamond | Level 26

How about:

data have;
  length variable_id $ 3 variable $ 3 number 8;
  infile datalines missover dlm=",";
  input variable_id variable number;
datalines;
001,TUR,1
001,TUR,1
002,TUR,0
002,TUR,0
003,ENG,1
003,ENG,1
004,GER,1
004,GER,1
005,FRA,1
005,FRA,1
;
run;
data have2;
  length variable $ 3 number 8;
  infile datalines missover dlm=",";
  input variable number;
datalines;
TUR,1
TUR,1
TUR,0
TUR,0
ENG,1
ENG,1
GER,1
GER,1
FRA,1
FRA,1
;
run;

data inter;
  set have (drop=variable_id) have2;
  if number;
run;

proc sql;
  create table WANT as
  select  distinct
          A.VARIABLE,
          (select count(VARIABLE) from INTER where VARIABLE=A.VARIABLE) / (select count(VARIABLE) from INTER) as PERCENT format=percent8.2
  from    INTER A;
quit;
LinusH
Tourmaline | Level 20

It's fun to see how to solve things in different ways.

But, as long as we are in the SAS world, remember to use the best tool for the situation, and for this situation PROC FREQ is better suited (IMHO) than SQL.

Data never sleeps
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Yes, but how often is it used...

/humour

turcay
Lapis Lazuli | Level 10

Thank you all for helping me 🙂

amandaokello
Calcite | Level 5
I like your "sass"!
Harmandeep
Fluorite | Level 6

This problem can be simplified by using a subquery for the count of total number of variable as :

 

DATA Have;
Length Variable_ID $ 3 Variable $ 3;
Infile Datalines Missover Dlm=",";
Input Variable_ID Variable;
Datalines;
001,TUR
001,TUR
002,TUR
002,TUR
003,ENG
003,ENG
004,GER
004,GER
005,FRA
005,FRA
;
Run;

 

Proc sql;

create table want as
select Variable, (Count(Variable) / (Select Count(Variable) from have)) * 100 as Percent
from have
group by Variable;

quit;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 41617 views
  • 6 likes
  • 9 in conversation