BookmarkSubscribeRSS Feed
zscott1
Calcite | Level 5

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.

27 REPLIES 27
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

zscott1
Calcite | Level 5

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.

Reeza
Super User

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

data_null__
Jade | Level 19

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

data_null__
Jade | Level 19

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Ksharp
Super User

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

stat_sas
Ammonite | Level 13

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)

zscott1
Calcite | Level 5

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

Reeza
Super User

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.

stat_sas
Ammonite | Level 13

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;

zscott1
Calcite | Level 5

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 27 replies
  • 1305 views
  • 7 likes
  • 6 in conversation