Macro variable question

[Bryan]

Writing a program to check if a record is present in a database (The database changes weekly) and if so, run some processing but the macro variable will not resolve to the correct value.  Always comes up zero? Program is below.

data Rcrds;

     input var1 $1 var2 3 ;

     cards;

A 1

A 1

C 0

E 0

;

run;

data List_to_Chk;

     input var1 $1;

     cards;

A

B

C

D

;

run;

proc sql;

     CREATE Table Tmp

           (var1 Char(13));    

quit;

%macro ads(var1, var2);

     data temp;

           %do j=1 %to &var2;

                var1="&var1"||&j;

                output;

           %end;

     run;

     proc sql;

           insert into Tmp

           select *

           from temp;

     quit;

%mend ads;

Data Results;

     set List_to_Chk;

     Call EXECUTE('proc sql noprint;

           select compress(put(count(*),8.)) into:cn

                from Rcrds

                where var1="'|| var1 ||'";');

     rc = resolve('&cn');

     put var1 rc;

     If rc > 0 then

           do;

                Check="Y";

                Call EXECUTE('%ads('||Var1||','||rc||')');

           end;

     Else Check="N";

Run;

[AncaTilea]

Hi.

I am not sure what's wrong with the code, but it seems to me that this SQL part will never be anything but zero, because in the Rcrds data set - the way you have it as an example -

var1 will never be equal to ' || var1 ||'

var1 in the data set is A, A,C, or E.

Call EXECUTE('proc sql noprint;

           select compress(put(count(*),8.)) into:cn

                from Rcrds

                where var1="'|| var1 ||'";');

So, you need to modify that, some what.

Best of luck,

Anca.

[Tom]

CALL EXECUTE does not execute the code immediately.  It just pushes the code onto the stack to execute after the currently running data step finishes.  So there is no way in the current data step to get the value of the macro variable CN that will be created in the future when your generated SQL code runs.

[ChrisHemedinger]

Instead of CALL EXECUTE you might try DOSUBL (but that works only if you have SAS 9.3 Maint 2 or later).  Although it seems to me there is probably a simpler approach.  I'll let the smarter folks weigh in on that.

[Bryan]

Thanks, This looks like it would work but I am on SAS 9.2

[Tom]

If your datasets are sorted already then a simple MERGE will get what you want.

data results;

   merge list_to_chk (in=in1) rcrds(in=in2);

   by var1;

   if first.var1 and in1 ;

   check = in2;

run;

Or if you prefer that CHECK be a character variable instead of a boolean number.

if in2 then check='Y' ;

else check = 'N';

[Bryan]

I though of a merge but I need to pass the count to the macro function to do some additonal processing.

[Astounding]

Bryan,

Once you get past this initial problem, it is likely this statement within %ADS will cause trouble:

var1 = "&var1"||&j;

This becomes something like:

var1 = "A"||1;

When you concatenate a number, SAS converts it to character (length=12), and adds leading blanks.  While it is not guaranteed, it is more likely you are looking for something like:

var1 = "&var1&j";

That would generate:

var1 = "A1";

Of course, it all depends on what you intend here.

Good luck.

[Bryan]

Thanks, did not find that yet so I can dequote it.

[Tom]

Normally I prefer to process the data all at once and use BY statements.  Even if you feel the need to be loopy you might want to generate a summary dataset with all of the counts using PROC SUMMARY or PROC FREQ and just pull the numbers from there rather than re-query the whole dataset for each individual possible value.

If you want to get a count how many records exists in a dataset for a particular value of a variable then PROC SQL can generate the macro variable for you. 

proc sql noprint ;

%let nobs=0;

select count(*) into :nobs separated by ' '

from have where var1 = "&value1"

;

quit;

Preset the value to zero because when there are no observations PROC SQL might not create the macro variable (not sure if it applies in this case).  Use the separated by clause will force an automatic strip() on the string generated to represent the number so that it does not have leading and/or trailing spaces.