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
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;
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;
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.
Like
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
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;
Thanks KurtBremser - the code worked for me, much appreciated for your help.
Regards,
Sara
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?
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: