BookmarkSubscribeRSS Feed
RTelang
Fluorite | Level 6

below code checks if the values of exported_dsn and outdsn are not matching if they are not matching then print the non matching observations.

 

%macro grp_key(lib=,lib2=,varlist=,indsn=,exported_dsn=,outdsn=,result_dsn=);
%local
key
hash
;
proc sql noprint;
select distinct catx(".",libname,memname) into: key separated by " "
from dictionary.columns
where libname ="&lib" and findw("&varlist",strip(name), ' ','i')>0;
quit;

%put &key;

data &indsn;
set &key;
run;

data &indsn;
set &indsn;
merge_key= _n_;
run;
proc sql noprint;
select distinct catx(".",libname,memname) into: hash separated by " "
from dictionary.columns
where libname ="&lib2" and findw("&varlist",strip(name),' ','i')>0;
quit;

%put &hash;

data &exported_dsn;
set &hash;
run;

%let newvarlist=;
%do i=1 %to %sysfunc(countw(&varlist));
rename %scan(&varlist,&i)=%scan(&varlist,&i).str;
%let newvarlist=&newvarlist%scan(&varlist,&i).str;
%end;

data &exported_dsn;
set &exported_dsn;
merge_key=_n_;
rename &varlist =&newvarlist;
run;

data both;
merge &indsn &exported_dsn;
by merge_key;
run;
PROC SORT DATA = both OUT = &outdsn NODUPKEY; BY &varlist &newvarlist; RUN;

PROC FREQ DATA=&outdsn;
TABLES &varlist*&newvarlist / noprint nopercent out=&outdsnfreq;
run;

proc sort data = &outdsnfreq nodupkey;
by &varlist &newvarlist;
run;

data &outdsnfreq;
set &outdsnfreq;
by &varlist &newvarlist;
if first.&varlist ne last.&varlist then do;
error = 1;
output;
end;
run;

proc print data = &outdsnfreq;
var &varlist &newvarlist;
run;

 

%mend grp_key;

%grp_key(lib=GRP,lib2=GKEY, varlist= name height,indsn=team1,exported_dsn=teams2,outdsn=total1,result_dsn=Gkey);

 


NOTE: Line generated by the macro variable "VARLIST".
1 name height
------
79
ERROR 79-322: Expecting a -.

NOTE: Line generated by the macro variable "NEWVARLIST".
1 name.strheight.str
------------------
24
ERROR: Missing numeric suffix on a numbered variable list (name-height).
ERROR: Invalid variable specification, name.strheight.str.
Variable names of the form X.X must be either FIRST.X or LAST.X.
MPRINT(GRP_KEY): rename name height =name.strheight.str ;
ERROR: Old and new variable name specifications for RENAME must be of the same type. Statement is
ignored.
MPRINT(GRP_KEY): run;

ERROR 24-322: Variable name is not valid

 

 

both libs hav 1 ds in them each with name height ID as columns in them, above code is appending data of indsn & exported_dsn and its merged in both dataset. in the proc compare am checking if values of both dataset are equal. But code gives error for &varlist.str and it doesnot check the specific each value from exported_dsn is matching to &outdsn.

 

How can i check if all exact specific values from exportdsn matc outdsn .....

 

15 REPLIES 15
ballardw
Super User

When you use a contruct like &varlist.str it places STR at the end of the value for varlist

Run this code and see the result in the log:

%let varlist = var1 var2 var3;

%put Resolves to: &varlist.str;

 

I really can't tell what you may have intended but if the goal was to get a result like:

Var1str Var2str Var3str you have a bit of work involved.

 

If you run your macros with:

 

options mprint symbolgen;

%ey(lib=GP,lib2=GK, varlist= name height,indsn=t1,exported_dsn=t2,outdsn=tot1,result_dsn=Gy);

 

The error message should appear in a location that makes sense and will show what your macro variables look like at execution.

 

BTW is there any reason you don't do:

data &exported_dsn;
   set &hash;

   merge_key = _n_;
run;

Instead of going through the data twice?

 

Proc compare is sort of notorious for sensitivity to sort differences as well. I can't tell from your code if the sets you are comparing are actually sorted the same. If the sets are not the same size you'll have a number of mismatches. Also since your shown proc compare is outside of the macro there could be an issue about the value of the dataset names in the macro variables due to scope.

RTelang
Fluorite | Level 6
@balladrw i have different data in both the datasets i first append data then i merge them, i want to check the data from exportdsn is matching the data in &outdsn & also see each unique value is matching another.
ballardw
Super User

Please post the working SAS procedure code that wrote before attempting to use macro code.

 

Did you run the first two example lines I provided that will show the most likely reasong that &varlist.str is not working?

What value do you expecte &varlist.str to produce?

RTelang
Fluorite | Level 6

@ballardw varlist values can't be renamed is the error.. can i do the check of unique values of export DS are matching unique values of outdsn. in diff way? can u suggest thanks

ballardw
Super User

@RTelang wrote:

@ballardw varlist values can't be renamed is the error.. can i do the check of unique values of export DS are matching unique values of outdsn. in diff way? can u suggest thanks


I can tell that the value is an error. What I cannot tell is what was actually intended. You placed it in code so you should have some idea what the ACTUAL value it should be for the example provided.

 

If you cannot tell us what the resolved value of the &varlist.str was supposed to look like then how can we help?

You should be able to say I wanted:

Namestr heightstr

 

BUT your use is incorrect in any case:

You have rename &varlist=&varlist.str

The syntax for rename would be

Rename Var1=NewVar1 Var2=NewVar2 Var3=NewVar3.

So you would above have to pull varlist apart, counting the number of elements and write separate bits:

The code for that would look something like:

%let newvarlist=;

   %do i = 1 %to %sysfunc(countw(&varlist));

   Rename  %scan(&varlist, &i) = %scan(&varlist,&i).str   ;

       %let newvarlist = &newvarlist %scan(&varlist,&i).str ;

    %end;

That will write one RENAME statement for each variable in &varlist and then create a new variable with the values of the renamed

variables.

 

Later you would refer to &newvarlist instead of &varlist.str

 

But I am guessing because you have not shown what the value you actually expect &varlist.str to be.

RTelang
Fluorite | Level 6
I have updated my code and the error can u help in correcting the error.....
Reeza
Super User

When you run macro code and get errors make sure to run with debugging options on so you can see your code and include the relevant section of the log, not just the error. Otherwise it's difficult to see which line generates the error. 

Reeza
Super User

This is a problem. It doesn't appear in a valid step as far as I can see. 

 


%do i=1 %to %sysfunc(countw(&varlist));
rename %scan(&varlist,&i)=%scan(&varlist,&i).str;
%let newvarlist=&newvarlist%scan(&varlist,&i).str;
%end;

 

When developing a macro you should be testing step by step rather than write a whole bunch of code and hoping it works. Go back to a step that works and move forward from there. 

 

 

RTelang
Fluorite | Level 6

@ballardw I tried your code still error exists and it doesn't check if any mismatch in the values.. cn u tak a look i hav posted updated code...

Reeza
Super User

IN addition to the RENAME statement the following is problematic. Not sure what you're expecting. 

 

data &indsn;
set &key;
run;

 

data &indsn;
set &indsn;
merge_key= _n_;
run;

These are the same so overwrite each other. 

 

So far I've pointed out two mistakes. Fix them and then see what errors still exist. 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Out of interest, are you intending to re-write the whole SAS package using your own macro code?

RTelang
Fluorite | Level 6
@RW9sorry can't understand wat u asking?
RW9
Diamond | Level 26 RW9
Diamond | Level 26

All of your posts are: I am writing macro code to do function xyz.  Base SAS is the programming language, it has all the data constructs, the functions, the processing, and can (and does, even in the case of macro code) do everything.  Hence why I am asking if it is some learning course or something where you have to re-write the whole Base SAS package, but using only macro language?  Otherwise almost all of you posts can be done far simpler in Base SAS, and half of the question become irrelevant.

RTelang
Fluorite | Level 6

@RW9 yes creating my own set of macros to perform certain specific function..So i do the base code then convert it into a generic macro, i.e to call the macro & perform its intended function instead of writing the code again & again.... in the above code am stuck at the point to check if specific unique values are matching particular values in both datasets... 

SAS Innovate 2025: Call for Content

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!

Submit your idea!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 15 replies
  • 3334 views
  • 1 like
  • 4 in conversation