@trevand wrote:
@Patrick I did exactly what you suggested. I appended all annual files and then saved them as a single year file since at the end I need single year files. How would you implement a check prior to the SQL and then only use the column in the SQL if it exists?
@ballardw you also suggested to do a check. Maybe you could also show an example of a check? There are indeed different number of variables in some of the years. I have different number of id variables that appear based on number of duplicates in each year. And the number of duplicates is not the same across the years.
@Tom I am doing a proc sql distinct group by. I'm not aware of a better alternative using data step.
How your code flows can make a difference on what approaches work for checking on variables.
Since it sound like you have a list of variables that may or may not exist this is an approach that checks the names in a list a against the contents of the data set and returns values in macro variable that only exist in the data set.
This sort of code would have to be used when the library and data set name are known along with the list of variables. So how it might be called depends on your code.
Caution: this has no error checking, so if you call the macro without a list of variables, memname (data set) or libname parameter it doesn't tell you. Also if you happen to pass a list with NO variables in common with the data set the resulting macro variable will be blank. You would have to test for that.
This places the result into a macro variable named Newvarlist. An exercise for the interested reader to add a parameter to create a differently named variable as a parameter..
%macro selectstr(varlist=,memname=, libname=,aliasvar=);
%let varlist=%upcase(&varlist.);
%put &varlist.;
proc sql noprint;
select catx('.', "&aliasvar.",name) into :newvarlist separated by ","
from dictionary.columns
where libname=upcase("&libname.") and memname=upcase("&memname.")
and findw("&varlist.",upcase(strip(name)),' ','i')>0
;
quit;
%global newvarlist;
%mend;
You have not provided any actual example of how your are using the problem variable names so I don't know if a data set name or alias needs to be included as is common in an SQL select list. i.e T1.var, T1.thatvar, t2.anothervar . The name would go in the Aliasvar. If it is missing or called as blank then no alias or dot appears.
An example call:
%selectstr(varlist=Name sex age mm nn,memname=class, libname=sashelp,aliasvar=t1);
and check the result with to see an example result
%put &newvarlist.;
Which in my case shows:
t1.Name,t1.Sex,t1.Age
So in use might look something like :
%selectstr(varlist=Name sex age mm nn,memname=class, libname=sashelp,aliasvar=t1);
proc sql;
create table work.example as
select &newvarlist, t1.height
from Sashelp.class as t1
where sex='F'
;
quit;
I show an example selecting an additional variable as your question has implied that you may have a smallish list that changes and the rest of the variables are present.
See that this has to be called completely before the Proc SQL that uses the Newvarlist and assumes that at least one variable remains.
