I have inherited a few hundred datasets and I'm going to merge them all using this method:
Proc SQL noprint;
Select 'lib.' || memname as ViewName
into :ViewNames separated by ' '
From DICTIONARY.lib
Where UPCASE(LIBNAME)='lib'
;quit;
data AllData;
set &ViewNames;
run;
My problem is that each data set only has one variable, an Item number. The name of the dataset is the name of the product that the item belongs to. I would like to add the name of the dataset as a variable to each record within the dataset before the merge. That way, once I merge the datasets together, I will have 2 variables, the ProductName and the ItemNumber. I was thinking that there may be an intermediate step to the logic I have above to accomplish this. That's why I included the code.
Again, I have a few hundred datasets so a way to loop through them all to accomplish this would be key.
Thanks for your help!
If you don't have sas 9.2, try this one:
libname test "c:\temp";
data test.aa;
input item;
cards;
8
;
data test.bb;
input item;
cards;
88
;
run;
proc sql noprint;
select memname into
:names separated by ' '
from dictionary.tables
Where libname='TEST'
;
quit;
/* add the dataset name to dataset*/
%macro add;
%do i=1 %to &sqlobs;
%let dsn=%sysfunc(scan(&names,&i));
data &dsn;
length dataset $ 32;
set test.&dsn ;
dataset=catx('.','test',"&dsn");
run;
%end;
%mend;
/* run the macro */
%add
/* run this code again to get the updated dataset */
proc sql noprint;
select memname into
:names separated by ' '
from dictionary.tables
Where libname='TEST'
;
quit;
/* create final dataset: test.all */
data test.all;
set &names;
run;
proc print data=&syslast;
title from &syslast;
run;
from TEST.ALL
Obs dataset item
1 test.AA 8
2 test.BB 88
are you sure your code works?
Proc SQL noprint;
Select 'lib.' || memname as ViewName
into :ViewNames separated by ' '
From DICTIONARY.lib
Where UPCASE(LIBNAME)='lib'
;quit;
data AllData;
set &ViewNames;
run;
do you have table "DICTIONARY.lib"?
Are you trying to do something like?:
%let lib=art;
libname &lib. "c:\art";
Proc SQL noprint;
Select "&lib.." || memname as ViewName
into :ViewNames separated by ' '
From DICTIONARY.tables
Where LIBNAME=UPCASE("&lib.")
;
quit;
data AllData;
set &ViewNames indsname=in_name;
dataset=in_name;
run;
If you have SAS 9.2+ you can use the indsname option.
data test;
set sashelp.class sashelp.cars indsname=dset;
source=dset;
run;
I should have mentioned: I'm on 9.1
If you don't have sas 9.2, try this one:
libname test "c:\temp";
data test.aa;
input item;
cards;
8
;
data test.bb;
input item;
cards;
88
;
run;
proc sql noprint;
select memname into
:names separated by ' '
from dictionary.tables
Where libname='TEST'
;
quit;
/* add the dataset name to dataset*/
%macro add;
%do i=1 %to &sqlobs;
%let dsn=%sysfunc(scan(&names,&i));
data &dsn;
length dataset $ 32;
set test.&dsn ;
dataset=catx('.','test',"&dsn");
run;
%end;
%mend;
/* run the macro */
%add
/* run this code again to get the updated dataset */
proc sql noprint;
select memname into
:names separated by ' '
from dictionary.tables
Where libname='TEST'
;
quit;
/* create final dataset: test.all */
data test.all;
set &names;
run;
proc print data=&syslast;
title from &syslast;
run;
from TEST.ALL
Obs dataset item
1 test.AA 8
2 test.BB 88
data x; input dsn $40.; cards; sashelp.class sashelp.shoes sashelp.air sashelp.buy ; run; data _null_; set x end=last; if _n_ eq 1 then call execute('proc sql;create table want as '); call execute(catx(' ','select "',dsn,'" as tname length=20,* from',dsn)); if not last then call execute('all outer union corresponding'); else call execute(';quit;'); run;
Ksharp
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 25. 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.