topic Re: search a specific library if they have specific datsets with required variables then append them in SAS Programming

search a specific library if they have specific datsets with required variables then append them

RTelang — Wed, 12 Oct 2016 05:09:54 GMT

my code checks the datasets in a library if they have the required variables then append them.

below is my code ---->

%macro chkvar(lib1=,lib2=,varlist=);

%local
list
ds
;

proc sql noprint;
select distinct catx(".",libname,memname) into :list separated by " "
from dictionary.columns
where libname = %upcase ("&lib1") and %upcase(name) in("&varlist") ;
quit;
%put &list;

data test1;
set &list;
run;

proc sql noprint;
select distinct catx(".",libname,memname) into :ds separated by " "
from dictionary.columns
where libname = %upcase ("&lib2") and %upcase(name) in("&varlist") ;
quit;

%put &ds;

data test2;
set &ds;
run;

%mend CHK;

%chkvar(lib1=detail,lib2=stats,varlist=%str('nam', 'DD', 'ht'));

but some how the code returns error of symbolic refrence to &list and &ds not found and also doedn't check the given library at all for the data-sets which hav the variables. ihave created 2 separate librarys 'detail' and 'stats' with the variables still it doesn't do the intented task.. can u suggest in any edit to rectify my code...

Re: search a specific library if they have specific datsets with required variables then append them

Reeza — Wed, 12 Oct 2016 05:47:17 GMT

Copy and paste your code to a clean editor, removing the %macro and %mend portion. Replace every macro variable with the parameter you pass in, literally copy and paste, and you'll see your mistake.

If you don't, try and run it and see your errors.

Your code is repitive so so you only need to do this for one PROC SQL.

Edit: Or check it against here, since I'm assuming this is the same person. http://stackoverflow.com/q/39872795/1919583

You didn't copy and paste correctly....

Poece of advice - I think this is probably your fourth question/thread on this topic at least. It's better to keep your question to one thread/question and then you can build on the answer and get help.

Re: search a specific library if they have specific datsets with required variables then append them

Reeza — Wed, 12 Oct 2016 05:20:27 GMT

Patrick's solution to when you posted this question earlier is correct.

Why not use his solution? If there's a portion you don't understand, ask for clarification but it's pretty much solution except no bugs.

Re: search a specific library if they have specific datsets with required variables then append them

RTelang — Wed, 12 Oct 2016 05:44:20 GMT

@Reeza his solution throws a error of parenthesis at the start of the macro not found. and the executing stops

Re: search a specific library if they have specific datsets with required variables then append them

Reeza — Wed, 12 Oct 2016 05:50:45 GMT

@RTelang wrote:
@Reeza his solution throws a error of parenthesis at the start of the macro not found. and the executing stops

Are you certain? It's possible but I can't see any mistakes in his code. Make sure to run it in a fresh session.

Worse case scenario type it out instead of copy and paste since the browser can introduce invisible characters.

Re: search a specific library if they have specific datsets with required variables then append them

Reeza — Wed, 12 Oct 2016 05:57:42 GMT

I tested it, you do get errors but not regarding the macro not found. The issue is the variable ID is in multiple datasets but has different types (char vs numeric) so you can't append then. Your method will have the same issue, unless your data is clean.

You also realize the code is appending the datasets with all variables not just the variables you've listed?

You may want to add a KEEP statement to the data step.

 54         %macro sap(lib=,varlist=);
 55         
 56           proc sql noprint;
 57             select distinct catx(".",libname,memname) into : datalist separated by " "
 58               from dictionary.columns
 59               where
 60                 libname = 'SASHELP'
 61                 and findw("&varlist",strip(name),' ','i')>0
 62                 and memtype='DATA'
 63             ;
 64           quit;
 65         
 66           data test1;
 67             set &datalist;
 68           run;
 69         
 70           proc print data=work.test1 (obs=20);
 71           run;
 72         
 73         %mend;
 74         
 75         
 76         %sap(lib=sashelp,varlist=name height);
 NOTE: PROCEDURE SQL used (Total process time):
       real time           0.04 seconds
       cpu time            0.04 seconds
       
 
 
 WARNING: Multiple lengths were specified for the variable Name by input data set(s). This can cause truncation of data.
 WARNING: Multiple lengths were specified for the variable Sex by input data set(s). This can cause truncation of data.
 WARNING: Multiple lengths were specified for the variable type by input data set(s). This can cause truncation of data.
 WARNING: Multiple lengths were specified for the variable subtype by input data set(s). This can cause truncation of data.
 WARNING: Multiple lengths were specified for the variable desc by input data set(s). This can cause truncation of data.
 NOTE: There were 322 observations read from the data set SASHELP.BASEBALL.
 NOTE: There were 19 observations read from the data set SASHELP.CLASS.
 NOTE: There were 19 observations read from the data set SASHELP.CLASSFIT.
 NOTE: There were 4 observations read from the data set SASHELP.FEEDER.
 NOTE: There were 159 observations read from the data set SASHELP.FISH.
 NOTE: There were 3600 observations read from the data set SASHELP.GRIDDED.
 NOTE: There were 5209 observations read from the data set SASHELP.HEART.
 NOTE: There were 27 observations read from the data set SASHELP.HOLIDAY.
 NOTE: There were 1703 observations read from the data set SASHELP.SLKWXL.
 NOTE: There were 2373 observations read from the data set SASHELP.SVRTDIST.
 NOTE: There were 11 observations read from the data set SASHELP.VBPLAYRS.
 NOTE: There were 44 observations read from the data set SASHELP._CMPIDX_.
 NOTE: The data set WORK.TEST1 has 13490 observations and 87 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.09 seconds
       cpu time            0.09 seconds