SAS MACROS question

[Santt0sh]

Hi All,

I am trying to update a table where the Country code 3 column is missing or null using the below macro. Kindly find the partial log after the macro;

I believe there is some issue with the way have written this macro because every time I run this macro I need to run it a couple of times to update all the missing records.

Kindly suggest.

options Symbolgen Mlogic Mprint;

%macro ints;
Proc sql ;
select Count(*) into :dnts from cre.FSC_COUNTRY_DIM Where
UPPER(country_code_3)=' ';
quit;

%put &dnts.;

%if &dnts. eq 0 %then
%do;
%put Note:Country DIM NOT EMPTY;
%end;
%else
%do;
%put &dnts.;

%do i=1 %to &dnts.;

Proc sql noprint;
select PARTY_NUMBER, STREET_COUNTRY_CODE into :pty_num1 -: pty_num&i.,
:cnty_cde1 -:cnty_cde&i. from cre.FSC_COUNTRY_DIM where
UPPER(country_code_3)=' ';
quit;
%put PARTY_NUMBER&i.==>> &&cnty_cde&i.;

proc sql noprint;
update cre.FSC_COUNTRY_DIM set country_code_3="&&cnty_cde&i." where
PARTY_NUMBER="&&pty_num&i." and UPPER(country_code_3)=' ';
quit;
%put country_code_3&i.;

%end;
%end;
%mend;

%ints;

=========================================================

LOGS:

SYMBOLGEN: Macro variable I resolves to 7
SYMBOLGEN: Macro variable PTY_NUM7 resolves to 0000000000xxxxxXXXXXYY
MPRINT(INTS): update cre.FSC_COUNTRY_DIM set country_code_3="XXX" where PARTY_NUMBER="0000000000xxxxxXXXXXYY" and
UPPER(country_code_3)=' ';
NOTE: 1 row was updated in CRE.FSC_COUNTRY_DIM.
MPRINT(INTS): quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds

MLOGIC(INTS): %PUT country_code_3&i.
SYMBOLGEN: Macro variable I resolves to 7
country_code_37
MLOGIC(INTS): %DO loop index variable I is now 8; loop will iterate again.
MPRINT(INTS): Proc sql noprint;
SYMBOLGEN: Macro variable I resolves to 8
SYMBOLGEN: Macro variable I resolves to 8
MPRINT(INTS): select PARTY_NUMBER, STREET_COUNTRY_CODE into :pty_num1 -: pty_num8, :cnty_cde1 -:cnty_cde8 from
cre.FSC_COUNTRY_DIM where UPPER(country_code_3)=' ';
MPRINT(INTS): ;
MPRINT(INTS): quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

MLOGIC(INTS): %PUT PARTY_NUMBER&i.==>> &&cnty_cde&i.
SYMBOLGEN: Macro variable I resolves to 8
SYMBOLGEN: && resolves to &.
SYMBOLGEN: Macro variable I resolves to 8
WARNING: Apparent symbolic reference CNTY_CDE8 not resolved.

[tarheel13]

Did you try writing it outside the macro prior to macrotizing it? 

[Quentin]

Hi,

You might be better off describing the big picture of what you're doing.  My guess is there is non-macro solution.

Just glancing at the code, and not really understanding what it's doing, I would think that:

Proc sql noprint;
select PARTY_NUMBER, STREET_COUNTRY_CODE into :pty_num1 -: pty_num&i.,
:cnty_cde1 -:cnty_cde&i. from cre.FSC_COUNTRY_DIM where
UPPER(country_code_3)=' ';
quit;

Should be before the %do block.  And maybe it should be:

Proc sql noprint;
select PARTY_NUMBER, STREET_COUNTRY_CODE into :pty_num1 -: pty_num&dnts.,
:cnty_cde1 -:cnty_cde&dnts. from cre.FSC_COUNTRY_DIM where
UPPER(country_code_3)=' ';
quit;

But again, if this is basically a data update problem, you might be better off with a MERGE or UPDATE step rather than writing values to macro variables and then writing them back to a dataset.

[Tom · Accepted solution]

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;