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.
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.
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.
That isn't enough information. For sampling, why not use proc surveyselect?
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();)');
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.
See Details. It's all in the details as they say.
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.
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
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)
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.
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;
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.
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.