I have the data with Yes and No responses. How to create the macro variable that gives total count for respective responses. for example in the following dataset I have two IDs with Y response. So I am expecting my macro variable that display the response count.
data x;
input id response$;
cards;
1 Y
2 Y
;
run;
In this case I am expecting to display the following
%put &resp_y &resp_n; ( I don't have any 'N' responses in the dataset but I still want to display 0 , SO, it has to come with 0 if there is no count, and want to display the value , if there is a value in future)
2 0
data x;
input id response $;
cards;
1 Y
2 Y
;
run;
%let Resp_y = 0;
%let Resp_n = 0;
proc sql noprint;
select count(*)
into :Resp_y
from x
where response = 'Y'
;
select count(*)
into :Resp_n
from x
where response = 'N'
;
quit;
%put Resp_y = &Resp_y;
%put Resp_n = &Resp_n;
data x;
input id response $;
cards;
1 Y
2 Y
;
run;
%let Resp_y = 0;
%let Resp_n = 0;
proc sql noprint;
select count(*)
into :Resp_y
from x
where response = 'Y'
;
select count(*)
into :Resp_n
from x
where response = 'N'
;
quit;
%put Resp_y = &Resp_y;
%put Resp_n = &Resp_n;
Thank you for quick response.
Show what you tried.
Normally SQL will create the macro variable even if the count is zero.
data x;
input id response $;
cards;
1 Y
2 Y
3 .
;
proc sql noprint;
select count(*)
, count(response)
, sum(response = 'Y')
, sum(response='N')
into :observations trimmed
, :non_missing trimmed
, :yes trimmed
, :no trimmed
from x
;
quit;
303 %put &=observations &=non_missing &=yes &=no ; OBSERVATIONS=3 NON_MISSING=2 YES=2 NO=0
Thank you
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.