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

check if multiple appended column values match values in another dataset

RTelang — Mon, 24 Oct 2016 07:18:00 GMT

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

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

ballardw — Fri, 21 Oct 2016 14:29:51 GMT

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.

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

RW9 — Fri, 21 Oct 2016 14:37:57 GMT

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

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

RTelang — Fri, 21 Oct 2016 14:40:21 GMT

@RW9sorry can't understand wat u asking?

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

RTelang — Fri, 21 Oct 2016 14:44:11 GMT

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

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

RW9 — Fri, 21 Oct 2016 14:55:18 GMT

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.

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

RTelang — Fri, 21 Oct 2016 15:03:45 GMT

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

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

ballardw — Fri, 21 Oct 2016 15:20:59 GMT

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?

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

RTelang — Fri, 21 Oct 2016 15:29:04 GMT

@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

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

ballardw — Fri, 21 Oct 2016 15:47:04 GMT

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

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

Reeza — Fri, 21 Oct 2016 15:59:49 GMT

@RTelang wrote:

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

If this is the case post your base code.

But again, you're doing thing inefficiently. At some point this will come back to bite you.

Look ok at the documentation for SAS 9.4 macros for examples on how to loop macro variables.

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

RTelang — Mon, 24 Oct 2016 06:50:21 GMT

I have updated my code and the error can u help in correcting the error.....

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

Reeza — Mon, 24 Oct 2016 06:53:13 GMT

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.

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

Reeza — Mon, 24 Oct 2016 07:33:53 GMT

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.

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

RTelang — Tue, 25 Oct 2016 08:48:56 GMT

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

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

Reeza — Tue, 25 Oct 2016 09:12:37 GMT

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.