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
Things ar getting more complicated. One quick question, which variable do you want to be checked for existance in fishdata?
the last variable, which I originally intended to be sea_depth
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);
great!
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.
While this code, does run, and give me the correct output for that variable it doesn't translate to "If the last variable in the dataset is '_____' or contains or has a name that contains " " then do....I want to be able to automate this for multiple datasets.
Thanks
Look at the SASHELP.VCOLUMN data set, specifically the varnum field.
Query the table and take the largest varnum and corresponding name will give you the last variable in each table. Generally speaking though relying on the order of variables in a table isn't considered good practice.
How would I pull automatically from there? I need the last variable of any dataset that I load.
Per my earlier post, just modify slightly to the below code. The first SQL queries the SASHELP.VCOLUMN table (the one which deals with columns in all the datasets within SAS), and puts the name of the last one (i.e. where the varnum is the max) into a macro variable for use later on. The second step creates a table with just this variable as an example of how to use.
proc sql;
select NAME
into :LAST_NAME
from SASHELP.VCOLUMN
where LIBNAME="SASHELP"
and MEMNAME="CARS"
and VARNUM=( select MAX(VARNUM)
from SASHELP.VCOLUMN
where LIBNAME="SASHELP"
and MEMNAME="CARS");
quit;
proc sql;
create table WORK.TMP as
select &LAST_NAME.
from SASHELP.CARS;
quit;
And if you don't like SQL;
proc sort data=sashelp.vcolumn out=work.tmp;
by descending varnum;
where libname="SASHELP" and memname="CARS";
run;
data _null_;
set work.tmp (obs=1);
call symput('LAST_NAME',name);
run;
data work.tmp;
set sashelp.cars (keep=&LAST_NAME.);
run;
Yes, this is possible to get last variable from the data set but if you are using the same data set for processing based on the extracted last variable then program will always process the first part because condition will %if &countnum>0 %then %do will always be true.
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.
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.