No need for to query twice to know how many macro variables you want to create. SAS will stop when it runs out of observations. And it will tell you how many it found in the automatic macro variable SQLOBS.
Here is a cleaned up version of your macro with some extra protection against special characters in the macro variables.
%macro ints;
%local i dnts party country;
proc sql noprint;
select distinct
PARTY_NUMBER
, STREET_COUNTRY_CODE
into :pty_num1-
, :cnty_cde1-
from cre.FSC_COUNTRY_DIM
where country_code_3=' '
;
%let dnts=&sqlobs;
%if &dnts. = 0 %then %do;
%put NOTE: No empty values of COUNTRY_CODE found.;
%end;
%else %do i=1 %to &dnts.;
%let party = %sysfunc(quote(%superq(pty_num&i),%str(%'))) ;
%let country = %sysfunc(quote(%superq(cnty_cde&i),%str(%'))) ;
update cre.FSC_COUNTRY_DIM
set country_code_3=&country
where party_number=&party
and country_code_3=' '
;
%end;
quit;
%mend;
%ints;
But really it looks like you just want to run this query.
proc sql noprint;
update cre.FSC_COUNTRY_DIM
set country_code_3=STREET_COUNTRY_CODE
where country_code_3=' '
;
quit;
... View more
