Conditionally executing a data step based upon last variable name or contents?

Reply
Contributor
Posts: 30

Conditionally executing a data step based upon last variable name or contents?

How would I do this?  Using macros?  I want to automate and run code if the last column/variable's name and/or content fits a certain naming and/or content criteria.

Super User
Super User
Posts: 7,988

Re: Conditionally executing a data step based upon last variable name or contents?

Yes, macros or call execute, e.g.

data _null_;

     set sasahelp.columns (where=(libname="YOURLIB" and memname="YOURDATASET"));

     if name="YOURCOLUMN" then call execute('%do_somecodemacro();');

run;

You would need to be more specific for a specific example.

Contributor
Posts: 30

Re: Conditionally executing a data step based upon last variable name or contents?

Specifically, let's say I want to automate sampling, proportional allocation stratify, if last variable name exists and/or contains something, SRS if it does not etc. automating as per the file loaded.

Super User
Posts: 19,867

Re: Conditionally executing a data step based upon last variable name or contents?

That isn't enough information. For sampling, why not use proc surveyselect?

Respected Advisor
Posts: 3,799

Re: Conditionally executing a data step based upon last variable name or contents?

In most cases when using CALL EXECUTE it is best to delay execution of the macro until after the data step finishes. 

call execute('%nrstr(%do_somecodemacro();)');

Super User
Super User
Posts: 7,988

Re: Conditionally executing a data step based upon last variable name or contents?

Posted in reply to data_null__

Sorry, I have to ask data_null_; how does the %nstr change the operation of call execute.  To my understanding call executes generates code which always executes after the containing step has finished irrespective of code used which is why it works particularly well as loop generation with a datastep.  E.g.

data _null_;

     set sashelp.cars;

     call execute('aaaa');

run;

When this gets sent to the compiler at each observation the text aaa would be put out to the compiler.  When the run; is encountered to end the datastep, the compiler then starts executing aaa then the next aaa etc. for however many were generated.

Respected Advisor
Posts: 3,799

Re: Conditionally executing a data step based upon last variable name or contents?

See Details.  It's all in the details as they say.

Super User
Super User
Posts: 7,988

Re: Conditionally executing a data step based upon last variable name or contents?

Posted in reply to data_null__

Thanks data _null_; I see what you mean now.  I don't really use macros so not really come across that.  Must try reading documentation sometime.

Super User
Posts: 10,044

Re: Conditionally executing a data step based upon last variable name or contents?

If your macro contains call symput ()  like :

%macro ......

.data _null_;

.........

call symputx( 'n', n);

run;

%if &n =1 %then %do;

..........

when you run macro by call execute(), you will get error message . therefore NULL's suggest is very good .

Xia Keshan

Trusted Advisor
Posts: 1,229

Re: Conditionally executing a data step based upon last variable name or contents?

You can try this one which first checks lastvar in the dataset have and based on that split the process

%macro check(dsname,var);
%let dsid=%sysfunc(open(&dsname));
%let countnum=%sysfunc(varnum(&dsid,&var));
%let rc=%sysfunc(close(&dsid));


%if &countnum>0 and &var='Contains something' %then %do;

Stratified sampling


%else %do;

SRS

%mend check;
%check(have,lastvar)

Contributor
Posts: 30

Re: Conditionally executing a data step based upon last variable name or contents?

I am now attempting this code w/ a small dummy set but got

WARNING: Argument 2 to function VARNUM referenced by the %SYSFUNC or %QSYSFUNC macro function is out

         of range.

NOTE: Mathematical operations could not be performed during %SYSFUNC function execution. The result

      of the operations have been set to a missing value. errors

proc sql;

create table blue.fishdata as select fish_I, fins,  sea_depth

from blue.fishdata;

quit;

%let dsname=blue.fishdata;

proc sql;

create table &dsname as select *

from blue.fishdata;

quit;

%macro check(dsname,var);

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

%let countnum=%sysfunc(varnum(&dsid,&var));

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

%if &countnum>0 and &var format=4. %then %do;

proc contents;

run;

%end;

%else %if &countnum format =2. %then %do;

proc sql;

select fish_I

from blue.fishdata;

quit;

%end;

%mend check;

%check(blue.fishdata,lastvar);

%check;

my total log is as follows:

555  proc sql;

556  create table blue.fishdata as select fish_I, fins,  sea_depth

557  from blue.fishdata;

WARNING: This CREATE TABLE statement recursively references the target table. A consequence of this

         is a possible data integrity problem.

NOTE: Table BLUE.FISHDATA created, with 9 rows and 3 columns.

558  quit;

NOTE: PROCEDURE SQL used (Total process time):

      real time           0.02 seconds

      cpu time            0.00 seconds

559

560

561

562  %let dsname=blue.fishdata;

563

564

565  proc sql;

566  create table &dsname as select *

567  from blue.fishdata;

WARNING: This CREATE TABLE statement recursively references the target table. A consequence of this

         is a possible data integrity problem.

NOTE: Table BLUE.FISHDATA created, with 9 rows and 3 columns.

568  quit;

NOTE: PROCEDURE SQL used (Total process time):

      real time           0.01 seconds

      cpu time            0.01 seconds

569

570  %macro check(dsname,var);

571  %let dsid=%sysfunc(open(&dsname));

572  %let countnum=%sysfunc(varnum(&dsid,&var));

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

574

575  %if &countnum>0 and &var format=4. %then %do;

576  proc contents;

577  run;

578  %end;

579  %else %if &countnum format =2. %then %do;

580  proc sql;

581  select fish_I

582  from blue.fishdata;

583  quit;

584

585  %end;

586  %mend check;

587  %check(blue.fishdata,lastvar);

588  %check;

WARNING: Argument 2 to function VARNUM referenced by the %SYSFUNC or %QSYSFUNC macro function is out

         of range.

NOTE: Mathematical operations could not be performed during %SYSFUNC function execution. The result

      of the operations have been set to a missing value.

Super User
Super User
Posts: 7,988

Re: Conditionally executing a data step based upon last variable name or contents?

Well, glad you took my solution on-board :smileylaugh:

What I think your problem is, is that you are creating a table - blue.fishdata - overwriting what is already there and only keeping three variables: fish_I, fins,  sea_depth

Then when you call the macro with: %check(blue.fishdata,lastvar);  there is no variable called lastvar in that dataset, hence you get an error.  If you are trying to find out how many variables there are in a dataset then query sashelp.vcolumn:

proc sql;

     select count(*)

     into :num_vars

     from     sashelp.vcolumn

     where   libname="SASHELP"

          and memname="CARS";

quit;

Super User
Posts: 19,867

Re: Conditionally executing a data step based upon last variable name or contents?

What are you trying to do overall? I think your script is overkill and that using the SASHELP.VCOLUMN table to get all the metadata about variables is a better approach.

Trusted Advisor
Posts: 1,229

Re: Conditionally executing a data step based upon last variable name or contents?

Few things need to be done to make it work.

proc sql;

create table fishdata as select fish_I, fins,  sea_depth /* Creating data in work lib */

from blue.fishdata;

quit;

%macro check(dsname,var);

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

%let countnum=%sysfunc(varnum(&dsid,&var));

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

%if &countnum>0 and &var format=4. %then %do;   /*Assign some value not format  like &var=4*/

proc contents data=&dsname;

run;

%end;

%else %do;

proc sql;

select fish_I

from &dsname;

quit;

%end;

%mend check;

%check(fishdata,lastvar); /* lastvar should be one of the variable in the dataset blue.fishdata,which are fish_I, fins,  sea_depth */

%check;

Contributor
Posts: 30

Re: Conditionally executing a data step based upon last variable name or contents?

should it be "lastvar" or "last.var"?  I made the fixes both of you suggested but still get the error.... this is now my log

1038  proc sql;

1039  create table fishdata as select fish_I, fins,  sea_depth

1040  from blue.fishdata;

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

1041  quit;

NOTE: PROCEDURE SQL used (Total process time):

      real time           0.26 seconds

      cpu time            0.00 seconds

1042

1043

1044

1045  %let dsname=fishdata;

1046  %macro check(dsname,var);

1047  %let dsid=%sysfunc(open(&dsname));

1048  %let countnum=%sysfunc(varnum(&dsid,&var));

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

1050

1051  %if &countnum>0  %then %do;

1052  proc contents data=&dsname;

1053  run;

1054  %end;

1055  %else %if &countnum<0 %then %do;

1056  proc sql;

1057  select fish_I

1058  from &dsname;

1059  quit;

1060

1061  %end;

1062  %mend check;

1063  options mprint mlogic symbolgen;

1064  %check(&dsname,lastvar);

MLOGIC(CHECK):  Beginning execution.

SYMBOLGEN:  Macro variable DSNAME resolves to fishdata

MLOGIC(CHECK):  Parameter DSNAME has value fishdata

MLOGIC(CHECK):  Parameter VAR has value lastvar

MLOGIC(CHECK):  %LET (variable name is DSID)

SYMBOLGEN:  Macro variable DSNAME resolves to fishdata

MLOGIC(CHECK):  %LET (variable name is COUNTNUM)

SYMBOLGEN:  Macro variable DSID resolves to 3

SYMBOLGEN:  Macro variable VAR resolves to lastvar

MLOGIC(CHECK):  %LET (variable name is RC)

SYMBOLGEN:  Macro variable DSID resolves to 3

SYMBOLGEN:  Macro variable COUNTNUM resolves to 0

MLOGIC(CHECK):  %IF condition &countnum>0 is FALSE

SYMBOLGEN:  Macro variable COUNTNUM resolves to 0

MLOGIC(CHECK):  %IF condition &countnum<0 is FALSE

MLOGIC(CHECK):  Ending execution.

1065  proc sql;

1066  create table fishdata as select fish_I, fins,  sea_depth

1067  from blue.fishdata;

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

1068  quit;

NOTE: PROCEDURE SQL used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

1069

1070

1071

1072  %let dsname=fishdata;

1073

1074

1075  proc sql;

1076  create table &dsname as select *

SYMBOLGEN:  Macro variable DSNAME resolves to fishdata

1077  from blue.fishdatas;

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

1078  quit;

NOTE: PROCEDURE SQL used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

1079

1080  %macro check(dsname,var);

1081  %let dsid=%sysfunc(open(&dsname));

1082  %let countnum=%sysfunc(varnum(&dsid,&var));

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

1084

1085  %if &countnum>0  %then %do;

1086  proc contents data=&dsname;

1087  run;

1088  %end;

1089  %else %if &countnum<0 %then %do;

1090  proc sql;

1091  select fish_I

1092  from &dsname;

1093  quit;

1094

1095  %end;

1096  %mend check;

1097  options mprint mlogic symbolgen;

1098  %check(&dsname,last.var);

MLOGIC(CHECK):  Beginning execution.

SYMBOLGEN:  Macro variable DSNAME resolves to fishdata

MLOGIC(CHECK):  Parameter DSNAME has value fishdata

MLOGIC(CHECK):  Parameter VAR has value last.var

MLOGIC(CHECK):  %LET (variable name is DSID)

SYMBOLGEN:  Macro variable DSNAME resolves to fishdata

MLOGIC(CHECK):  %LET (variable name is COUNTNUM)

SYMBOLGEN:  Macro variable DSID resolves to 3

SYMBOLGEN:  Macro variable VAR resolves to last.var

WARNING: Argument 2 to function VARNUM referenced by the %SYSFUNC or %QSYSFUNC macro function is out

         of range.

NOTE: Mathematical operations could not be performed during %SYSFUNC function execution. The result

      of the operations have been set to a missing value.

MLOGIC(CHECK):  %LET (variable name is RC)

SYMBOLGEN:  Macro variable DSID resolves to 3

SYMBOLGEN:  Macro variable COUNTNUM resolves to .

MLOGIC(CHECK):  %IF condition &countnum>0 is FALSE

SYMBOLGEN:  Macro variable COUNTNUM resolves to .

MLOGIC(CHECK):  %IF condition &countnum<0 is TRUE

MPRINT(CHECK):   proc sql;

SYMBOLGEN:  Macro variable DSNAME resolves to fishdata

MPRINT(CHECK):   select fish_I from fishdata;

MPRINT(CHECK):   quit;

NOTE: PROCEDURE SQL used (Total process time):

      real time           0.01 seconds

      cpu time            0.00 seconds

MLOGIC(CHECK):  Ending execution.

Ask a Question
Discussion stats
  • 27 replies
  • 529 views
  • 7 likes
  • 6 in conversation