topic Re: Conditionally executing a data step based upon last variable name or contents? in SAS Programming

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

zscott1 — Wed, 09 Jul 2014 15:20:40 GMT

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.

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

RW9 — Wed, 09 Jul 2014 15:24:03 GMT

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.

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

zscott1 — Wed, 09 Jul 2014 15:50:30 GMT

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.

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

Reeza — Wed, 09 Jul 2014 16:08:43 GMT

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

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

stat_sas — Wed, 09 Jul 2014 16:20:01 GMT

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)

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

data_null__ — Wed, 09 Jul 2014 17:13:12 GMT

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();)');

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

RW9 — Wed, 09 Jul 2014 17:57:12 GMT

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.

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

data_null__ — Wed, 09 Jul 2014 18:42:20 GMT

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

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

RW9 — Thu, 10 Jul 2014 07:48:34 GMT

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.

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

zscott1 — Thu, 10 Jul 2014 16:42:15 GMT

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.

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

RW9 — Thu, 10 Jul 2014 17:01:12 GMT

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;

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

Reeza — Thu, 10 Jul 2014 17:03:59 GMT

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.

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

stat_sas — Thu, 10 Jul 2014 17:42:13 GMT

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;

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

zscott1 — Thu, 10 Jul 2014 18:25:39 GMT

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.

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

zscott1 — Thu, 10 Jul 2014 18:29:46 GMT

and for some reason it says I have 15 vars? which i don't i have 3 w/ true values and i got rid of blank column v4 by using the sql statement

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

stat_sas — Thu, 10 Jul 2014 18:37:08 GMT

Things ar getting more complicated. One quick question, which variable do you want to be checked for existance in fishdata?

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

zscott1 — Thu, 10 Jul 2014 18:54:31 GMT

the last variable, which I originally intended to be sea_depth

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

stat_sas — Thu, 10 Jul 2014 19:06:17 GMT

Try this one hopefully should work.

proc sql;

create table fishdata as select fish_I, fins, sea_depth

from blue.fishdata;

%let dsname=fishdata;

options mprint mlogic symbolgen;

%macro check(dsname,var);

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

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

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

%if &countnum>0 %then %do;

proc contents data=&dsname;

run;

%end;

%else %do;

proc sql;

select fish_I

from &dsname;

quit;

%end;

%mend check;

%check(&dsname,sea_depth);

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

zscott1 — Thu, 10 Jul 2014 19:46:47 GMT

great!

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

zscott1 — Thu, 10 Jul 2014 20:33:51 GMT

Actually, there still is a problem... When I call the macro, I must call the variable name which is data specific, I want a macro that I can automate to call the last variable in the set, or the last variable sas can find which is populated.