Hi ,
I have a dataset Test1 which has these columns Placebo,RBC,WBC,Weight,Number.Another Dataset Test2 which looks like below. In Dataset Test2 I have taken list of items I need to check in Data Test1 whether they exist or not . And for the match ones i need to create Test3 which will have common variables which are in both Test1 and Test2 dataset using below code i am able to check all the variables
Test2
List | Freq |
RBC | Count |
WBC | Treatment |
Placebo | Other,Race,Weight |
proc contents data=test1 out=sdetail (keep=name varnum);
run;
proc sql;
create table Test3 as
select List,Freq
,(upcase(List) in (select upcase(name) from sdetail)) as var1
,(upcase(Freq) in (select upcase(name) from sdetail)) as var2
from test2
create table Test7 as
select List , Freq from test3
where var1=1 and var2=1
;
quit;
But I am able to check Count,Treatment RBC,WBC in test2 whether they are in test1 but how can i check when variables in Freq seperated by commas using above code like other,Race,Weight
Can anyone please help
Thank you
If you're checking if two data sets have the same variables, have you considered PROC COMPARE?
I also have a little routine here that creates a data set that identifies which variables are in which data sets.
%macro compare_data(base=, compare=); proc sql noprint; create table base_in as select name from sashelp.vcolumn where libname=upper(scan("&base", 1, ".")) and memname=upper(scan("&base", 2, ".")) order by varnum; quit; proc sql noprint; create table compare_in as select name from sashelp.vcolumn where libname=upper(scan("&compare", 1, ".")) and memname=upper(scan("&compare", 2, ".")) order by varnum; quit; proc sql; create table comparison as select a.name as base_var, b.name as compare_var, case when missing(a.name) then catx("-", "Comparison dataset has extra variable", b.name) when missing(b.name) then catx("-", "Comparison dataset is missing variable", a.name) when a.name=b.name then "Variable in both datasets" else "CHECKME" end as comparison from base_in as a full join compare_in as b on a.name=b.name; quit; data _null_; set comparison; if comparison not eq "Variable in both datasets" then put "ERROR:" comparison; run; %mend; /*example of calling macro*/ data class; set sashelp.class; drop sex age; weight2=weight*2; run; %compare_data(base=sashelp.class, compare=work.class);
https://gist.github.com/statgeek/e0b98c4627aa31a567e5
Thank you . But I wanted test3 table which is a result of var1=1 and var2=1 .
If both RBC and Count from test2 present in test1 then var1=1 and var2=1 | we need this row in test3table |
If WBC from test2 is present in test1 and Treatment is not present in test1 then var1=1 and var2=0 | we don’t need this row in test3 table |
but how do I check when variables seperated by commas | ? |
Thank you
Please stop posting code as pictures. There is above the post area a code window - its the {i} - where you can copy text from your editor and post it into the code window here. This keeps formatting (mostly), and allows other users to copy and paste the code as well.
Hi ,
I have tried it didn't work . I wanted something like when Freq has variables which are separated by commas we should use some index to check if we have comma and then if yes loop through all variables to check whether each variable is present in sdetail dataset
Thanks
/*1*/
proc contents data=test1 out=sdetail (keep=name) noprint;
run;
data sdetail;
set sdetail;
name=upcase(name);
run;
data test7;
if 0 then set test2 sdetail(keep=name);
if _n_=1 then do;
declare hash myhash(dataset:'sdetail');
myhash.definekey('name');
myhash.definedone();
end;
set test2;
length temp1 $32;
length temp2 $32;
n1=countw(List, ',');n2=countw(Freq, ',');
Flag1=1;Flag2=1;
do i=1 to n1;
temp1=upcase(scan(List,i,','));
if myhash.check(key:temp1)^=0 and temp1^='' then do;
Flag1=0;leave;
end;
end;
if Flag1=1 then do i=1 to n2;
temp2=upcase(scan(Freq,i,','));
if myhash.check(key:temp2)^=0 and temp2^='' then do;
Flag2=0;leave;
end;
end;
if Flag1 and Flag2;
keep List Freq;
run;
/* 2*/
proc contents data=test1 out=sdetail (keep=name) noprint;
run;
%let strFieldList=;
proc sql noprint;
select name into :strFieldList separated by ',' from sdetail;
quit;
%put &strFieldList;
data test7;
set test2;
length temp1 $32;
length temp2 $32;
n1=countw(List, ',');n2=countw(Freq, ',');
Flag1=1;Flag2=1;
do i=1 to n1;
temp1=scan(List,i,',');
if temp1^='' and find("&strFieldList",temp1,'it')=0 then do;
Flag1=0;leave;
end;
end;
if Flag1=1 then do i=1 to n2;
temp2=scan(Freq,i,',');
if temp2^='' and find("&strFieldList",temp2,'it')=0 then do;
Flag2=0;leave;
end;
end;
if Flag1 and Flag2;
keep List Freq;
run;
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!
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.