topic SQL through a list of file in SAS Programming

SQL through a list of file

hhchenfx — Tue, 09 Oct 2018 22:56:56 GMT

Hi,

I have a list of file with name say, one two three ...

They all have the same number of row and they all have a variable date.

I want to run the below SQL code on all combinations of file.

so there will be ouput file onetwo ,onethree, twothree .

Can you help me to create a Loop to do it?

Thank you,

HHCFX

proc sql;
create table onetwo
as select * from one join two
from a.date=b.date;quit;

Re: SQL through a list of file

ballardw — Tue, 09 Oct 2018 23:23:53 GMT

Do you have the list of names in a data set? Does it include any needed library to find the sets? If you have a library name what do want for the output?

What is longest name of the datasets involved? If your names are longer than 16 characters you have a strong possibility of exceeding the 32 character limit for data set names the way you combine them.

This may get you started:

data listdataset;
   informat name $41.;
   input name;
datalines;
one
two 
three
alongerset
;
run;

proc sql;
   create table control as
   select a.name, b.name as bname
   from listdataset as a, listdataset as b
   where a.name lt b.name
   ;
quit;

data _null_;
   set control;
   call execute ('proc sql; create table '||
      cats(name,bname)||'as select'|| 
        catx(',',catx(',',name,'*'), catx(',',bname,'*'))||
     'from '|| name||' join '|| bname ||
     'from ' catx('=',catx('.',name,'date'), catx('.',bname,'date') )||
    ';quit;');
run;

Caution: if combinations of your data set names aren't unique you won't get what you want:

Example:

TE NT and

T ENT would both try to create TENT. So only the last combination run would exist.

If that might happen you would need to insert _ character between names to create TE_NT and T_ENT. Which reduces the number of characters allowed in the data set names by one per possible combination.

You are going to get a low of warnings about the variable DATE in both sets. If you have any other variables with the same name in the data sets your results are unpredictable and possibly generate errors if they are not of the same type.

Re: SQL through a list of file

hhchenfx — Tue, 09 Oct 2018 23:49:57 GMT

here we go

	%let Rate_list= one two three;

	%macro BUYcode;
	%do i=1 %to %sysfunc(countw(&Rate_list));
			%let rate1=%scan(&Rate_list,&i);
	%do j=&i+1 %to %sysfunc(countw(&Rate_list));
			%let rate2=%scan(&Rate_list,&j);

			proc sql;
			create table &rate1._&rate2
			as select *
			from &rate1 as a join &rate2 as b
			on a.date=b.date;quit; 	
	%end;
	%end;
	%mend;

%BUYcode;