topic Re: How to create Macro Variable using SQL in SAS Programming

How to create Macro Variable using SQL

SASuserlot — Wed, 15 Dec 2021 22:00:23 GMT

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

Re: How to create Macro Variable using SQL

SASKiwi — Wed, 15 Dec 2021 22:22:18 GMT

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;

Re: How to create Macro Variable using SQL

SASuserlot — Wed, 15 Dec 2021 22:40:48 GMT

Thank you for quick response.

Re: How to create Macro Variable using SQL

Tom — Thu, 16 Dec 2021 02:04:07 GMT

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

Re: How to create Macro Variable using SQL

SASuserlot — Thu, 16 Dec 2021 04:14:59 GMT

Thank you