topic Re: Check columns whether they are present in SAS Programming

Check columns whether they are present

hexx18 — Mon, 10 Dec 2018 05:03:23 GMT

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

Re: Check columns whether they are present

Reeza — Mon, 10 Dec 2018 03:45:29 GMT

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

Re: Check columns whether they are present

hexx18 — Mon, 10 Dec 2018 05:35:43 GMT

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

Re: Check columns whether they are present

learsaas — Mon, 10 Dec 2018 07:41:53 GMT

Re: Check columns whether they are present

RW9 — Mon, 10 Dec 2018 08:58:01 GMT

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.

Re: Check columns whether they are present

hexx18 — Mon, 10 Dec 2018 17:13:18 GMT

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

Re: Check columns whether they are present

Reeza — Mon, 10 Dec 2018 17:26:02 GMT

It may help if you explain what 'didn't work' and show a better example of what you have and what you need.

Re: Check columns whether they are present

learsaas — Tue, 11 Dec 2018 09:04:54 GMT

/*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;