How to create Macro Variable using SQL

[SASuserlot]

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

[SASKiwi · Accepted solution]

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; 

[SASuserlot]

Thank you for quick response.

[Tom]

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

[SASuserlot]

Thank you