topic Re: select specific column from one table to combine with other table in SAS Programming

select specific column from one table to combine with other table

saraphdnz — Wed, 22 Apr 2020 05:05:58 GMT

Hi there,

I have multiple sas tables and each table have multiple columns.

Like one library contain tables Myfiles.Jan1990, Myfiles.Feb1990,.... Myfiles.Dec2016

another library contain tables Myfiles1.estJan1990, Myfiles1.estFeb1990,.....Myfiles1.estDec2016. (same name columns in each table in this library).

I need to combine a specific column (named as Intercept) from Myfiles1 tables with Myfiles tables.

Is it possible to combine a specific column from one table with the bunch of columns in other table? I have attached two files from both libraries for reference.

Regards,

Sara

Re: select specific column from one table to combine with other table

unison — Wed, 22 Apr 2020 05:52:14 GMT

I'd first see if there was a way to adjust the process that creates all of these separate tables in order to avoid that. If that's not possible, try something like this:

*Data setup to replicate the datasets you should have;
data _null_;
	start_dt='01JAN1990'd;
	end_dt='01DEC2016'd;
	dt=start_dt;
	i=0;

	do until(dt>end_dt);
		curr_dt=put(dt, monyy7.);
		call execute(catx('', 'data', curr_dt, ';message = "Hi from', curr_dt, 
			'";run;') );
		call execute(catx('', 'data', cats('est', curr_dt), 
			';message2 = "Hi from estimate:', curr_dt, '";run;') );
		i+1;
		dt=intnx('month', start_dt, i, 'b');
	end;
run;

*Get table names -- you may have to adjust WORK to MYFILES;
proc sql;
	create table _tabs as select memname as table_name from dictionary.tables 
		where libname='WORK' and lengthn(memname)=7 order by input(memname, monyy7.);
quit;


/***
Read through table names and execute code that looks like:

data wantJAN1990;
	merge JAN1990(in=a) estJAN1990(in=b);
	if b;
run;

***/

data _null_;
	set _tabs;
	call execute(catx('',
		'data',cats('want', table_name), 
		'; merge',cats(table_name, '(in=a)'), cats('est', table_name, '(in=b)'),
		'; if a; run;'
	));
run;

*Combine all want datasets  together;
data final_want;
	set want:;
run;

*Preview results;
proc print data=final_want(obs=20);
run;

Re: select specific column from one table to combine with other table

Kurt_Bremser — Wed, 22 Apr 2020 09:53:57 GMT

First, stop naming your datasets like this. If you need timeseries, use a properly sorting naming scheme, so estjan1990 has to be called est_1990_01. Create a generic name for the other datasets, if you don't have one, use a simple underline.

And what is the reason for that massive redundancy in the EST dataset? The only value in there is -1, and it is always in the column named in column _DEPVAR_.

What is the expected result from the intended join?

Re: select specific column from one table to combine with other table

saraphdnz — Wed, 22 Apr 2020 11:22:59 GMT

I think I didn't explain my query properly.

I just want to combine one column variable named as INTERCEPT from each of MYFILES1 table to combine with the MYFILES table.

MYFILES.estjan1990 column variable "INTERCEPT" merge with MYFILES.jan1990 table

MYFILES.estFeb1990 column variable "INTERCEPT" merge with MYFILES.Feb1990 table

I used the below macro which merge all columns but I don't want all columns to merge.

/* Macro for multiple sheet*/

%let months = JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC;
%macro ex;
%do year = 1990 %to 2016; /* adapt years as need */
    %do i=1 %to 12;
        %let file = %scan(&months,&i)&year;
        %put FILE=&file;
        /* enter here your code */
       
       data Myest.&file;
	merge myfiles1.&file(in=a) myfiles.myest&file(in=b);
	if b;
run;

/* enter here your code */

    %end;
%end;
%mend ex;
%ex;

hope it suffices.

Regards,

Sara

Re: select specific column from one table to combine with other table

Kurt_Bremser — Wed, 22 Apr 2020 11:59:21 GMT

How about these changes to the data step:

data Myest.&file;
merge
  myfiles1.&file (in=a)
  myfiles.myest&file (
    in=b
    keep=_depvar intercept
    rename=(_depvar_=date)
  )
;
by date;
if b;
run;

Re: select specific column from one table to combine with other table

saraphdnz — Thu, 23 Apr 2020 04:16:02 GMT

Thanks KurtBremser - the code worked for me, much appreciated for your help.

Regards,

Sara