BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
RTelang
Fluorite | Level 6

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...
 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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
       

View solution in original post

5 REPLIES 5
Reeza
Super User

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. 

Reeza
Super User

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. 

RTelang
Fluorite | Level 6
@Reeza his solution throws a error of parenthesis at the start of the macro not found. and the executing stops
Reeza
Super User

@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. 

Reeza
Super User

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
       

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
  • 5 replies
  • 910 views
  • 1 like
  • 2 in conversation