Help using Base SAS procedures

check if multiple appended column values match values in another dataset

Reply
Regular Contributor
Posts: 190

check if multiple appended column values match values in another dataset

[ Edited ]

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

 

Super User
Posts: 11,343

Re: check if multiple appended column values match values in another dataset

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.

Regular Contributor
Posts: 190

Re: check if multiple appended column values match values in another dataset

@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.
Super User
Posts: 11,343

Re: check if multiple appended column values match values in another dataset

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?

Regular Contributor
Posts: 190

Re: check if multiple appended column values match values in another dataset

[ Edited ]

@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

Super User
Posts: 11,343

Re: check if multiple appended column values match values in another dataset


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.

Regular Contributor
Posts: 190

Re: check if multiple appended column values match values in another dataset

I have updated my code and the error can u help in correcting the error.....
Super User
Posts: 19,862

Re: check if multiple appended column values match values in another dataset

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. 

Super User
Posts: 19,862

Re: check if multiple appended column values match values in another dataset

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. 

 

 

Regular Contributor
Posts: 190

Re: check if multiple appended column values match values in another dataset

[ Edited ]

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

Super User
Posts: 19,862

Re: check if multiple appended column values match values in another dataset

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. 

Super User
Super User
Posts: 7,988

Re: check if multiple appended column values match values in another dataset

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

Regular Contributor
Posts: 190

Re: check if multiple appended column values match values in another dataset

@RW9sorry can't understand wat u asking?
Super User
Super User
Posts: 7,988

Re: check if multiple appended column values match values in another dataset

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.

Regular Contributor
Posts: 190

Re: check if multiple appended column values match values in another dataset

[ Edited ]

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

Ask a Question
Discussion stats
  • 15 replies
  • 504 views
  • 1 like
  • 4 in conversation