DATA Step, Macro, Functions and more

Add the dataset name as a variable in the dataset

Accepted Solution Solved
Reply
Contributor
Posts: 54
Accepted Solution

Add the dataset name as a variable in the dataset

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!


Accepted Solutions
Solution
‎04-03-2012 01:51 PM
Super Contributor
Posts: 1,636

Re: Add the dataset name as a variable in the dataset

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

View solution in original post


All Replies
Super Contributor
Posts: 1,636

Add the dataset name as a variable in the dataset

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;

Contributor
Posts: 54

Re: Add the dataset name as a variable in the dataset

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. Smiley Wink 

Super Contributor
Posts: 1,636

Re: Add the dataset name as a variable in the dataset

do you have table "DICTIONARY.lib"?    

PROC Star
Posts: 7,363

Re: Add the dataset name as a variable in the dataset

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;

Super User
Posts: 17,864

Re: Add the dataset name as a variable in the dataset

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

data test;

set sashelp.class sashelp.cars indsname=dset;

source=dset;

run;

Contributor
Posts: 54

Re: Add the dataset name as a variable in the dataset

I should have mentioned: I'm on 9.1 Smiley Sad

Solution
‎04-03-2012 01:51 PM
Super Contributor
Posts: 1,636

Re: Add the dataset name as a variable in the dataset

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

Super User
Posts: 9,682

Re: Add the dataset name as a variable in the dataset

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 2586 views
  • 1 like
  • 5 in conversation