Small Macro program

Reply
Contributor
Posts: 66

Small Macro program

I have a number of data set in a SAS lib.

In this example I have 3 data sets have1, have2 and have3.

I want to create  three data sets want1, want2 and want3 as follows

-Each new data sets will now have an extra varibale 'NewVar' which is the maximum value of variable 'b' in  corresponding 'have' data sets.

-If the data sets 'have's has variable b then the new variable will extract the maximum value of this variable, other wise (if it does not have variable b as in have2) then the newVar will be 999.

This is my attempt and it does not work.

Any help would be highly appreciated.

Thanks,

Raghu.

data have1;
input a b c;
datalines;
1 2 3
3 5 1
4 1 2
;
data have2;
input a  c;
datalines;
4 2
3 6
4 1
;
data have3;
input b c;
datalines;
3 2
3 5
7 1
;

%Macro want;

   %do i=1 %to 3;
  %if %varexist(have&i,b) then %DO;

    proc sql noprint;
  select max(b) into:maxB
  from have&i;
  quit;

  data want&i;
  set have&i;
  NewVar=&maxB;
  run;
  %else
  data want&i;
  set have&i;
  NewVar=999;
  run;
 
%end;
%Mend;
%want;

Super User
Posts: 19,770

Re: Small Macro program

For starters, what's %varexist? Is that another macro you have defined somewhere? If so you'll need to post that code as well.

Second make sure your %do have a matching %end, count them if you need to.

Third, make sure your first loop works then move on to the second (hint: %then, in macro code).

Fourth, I think you may need an %else %do; rather than just a %else.

Codes below (highlight) if you don't feel like debugging and just want the answer.

%macro VarExist(ds,var);

    %local rc dsid result;

    %let dsid=%sysfunc(open(&ds));

    %if %sysfunc(varnum(&dsid,&var)) > 0 %then %do;

        %let result=1;

        %put NOTE: Var &var exists in &ds;

    %end;

    %else %do;

        %let result=0;

        %put NOTE: Var &var not exists in &ds;

    %end;

    %let rc=%sysfunc(close(&dsid));

    &result

%mend VarExist;

%Macro want;

   %do i=1 %to 3;

  %if %varexist(have&i,b) %then %DO;

    proc sql noprint;

    create table want&i as

  select *, max(b) as NewVar

  from have&i;

  quit;

%end;

%else %do;

  data want&i;

  set have&i;

  NewVar=999;

  run;

%end;

%end;

 

%Mend;

%want;

Contributor
Posts: 66

Re: Small Macro program

From my understanding

%if %varexist(have&i,b)  checks if variable 'b' exists in data set have&i.

If it exists then NewVar=max(b)  otherwise newVar=999.

Thanks.

Super User
Posts: 19,770

Re: Small Macro program

You have to define the macro though...

Super User
Super User
Posts: 7,039

Re: Small Macro program

You probleme is Miss matched DO/END

%Macro want;

%do i=1 %to 3;
  %if %varexist(have&i,b) then %DO;

    proc sql noprint;
  select max(b) into:maxB
  from have&i;
  quit;

  data want&i;
  set have&i;
  NewVar=&maxB;
  run;

    %END;
  %else %DO;
  data want&i;
  set have&i;
  NewVar=999;
  run;
   %END;
%end;
%Mend;

Contributor
Posts: 66

Re: Small Macro program

Tom, I tried with this one, it still does not work. Do not understand why the log still shows DO/End issue.

Thanks.

Super User
Super User
Posts: 7,039

Re: Small Macro program

Missing % on the %THEN

Super User
Super User
Posts: 7,039

Re: Small Macro program

%macro want;

proc sql noprint ;

%do i=1 %to 2;

  create table want&i as

    select *,

  %if %varexist(have&i,b) %then max(b);  %else 999 ;

       as maxb

    from have&i

  ;

%end;

quit;

%mend want ;



data have1 have2(drop=b);

do a=1 to 2; do b=1 to a; output; end; end;

run;

options mprint;

%want;

MPRINT(WANT):   proc sql noprint ;

MPRINT(WANT):   create table want1 as select *,

MPRINT(WANT):   max(b) as maxb from have1 ;

NOTE: The query requires remerging summary statistics back with the original data.

NOTE: Table WORK.WANT1 created, with 3 rows and 3 columns.

MPRINT(WANT):   create table want2 as select *,

MPRINT(WANT):   999 as maxb from have2 ;

NOTE: Table WORK.WANT2 created, with 3 rows and 2 columns.


MPRINT(WANT):   quit;


Ask a Question
Discussion stats
  • 7 replies
  • 236 views
  • 0 likes
  • 3 in conversation