topic count comman variables in work libraray in SAS Programming

count comman variables in work libraray

BrahmanandaRao — Mon, 15 Feb 2021 13:09:19 GMT

	data ds;
	set sahelp.class;
	run;

	
	data ds1;
	set sashelp.class;
	run;


 proc sql;
	select Name,count(Name) 
	from dictionary.columns
	where libname='WORK'
	group by name
	having count(Name)>2;
	quit;

no output

i want output comman variables count in work libraray

Re: count comman variables in work libraray

maguiremq — Mon, 15 Feb 2021 13:28:20 GMT

I'm not quite sure I understand why you're counting variables. Could you explain? The code below will return common variables between data sets, but that doesn't seem to be what you want according to your post.

Edit: I meant to use the intersect operator.

proc sql;
	select
				name
	from
				dictionary.columns
	where
				libname = "WORK"	and
				memname = "DS"
	intersect
	select
				name
	from
				dictionary.columns
	where	
				libname = "WORK" 	and
				memname = "DS1";
quit;

Re: count comman variables in work libraray

SASJedi — Mon, 15 Feb 2021 13:31:32 GMT

Your first DATA step is not executing because you misspelled "sashelp" as "sahelp". First, correct that.

Next, your SQL query asks for rows where the count is >2. Because you only have 2 data sets created, no rows meet the criteria, so no output.

With the code corrected:

data ds;
	set sashelp.class;
run;

data ds1;
	set sashelp.class;
run;

 proc sql;
select Name,count(Name) 
	from dictionary.columns
	where libname='WORK'
	group by name
	having count(Name)>=2;
quit;

You get results:

Column Name
Age	2
Height	2
Name	2
Sex	2
Weight	2