Add the dataset name as a variable in the dataset

[Mishka1]

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!

[Linlin]

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;

[Mishka1]

The lowercase 'lib' should be upper case LIB

Sorry, I tried to pair the code down to just the nuts and bolts and also to sanitize my corporate owned code.  

[Linlin]

do you have table "DICTIONARY.lib"?    

[art297]

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;

[Reeza]

If you have SAS 9.2+ you can use the indsname option.

data test;

set sashelp.class sashelp.cars indsname=dset;

source=dset;

run;

[Mishka1]

I should have mentioned: I'm on 9.1

[Linlin · Accepted solution]

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

[Ksharp]

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