topic Add the dataset name as a variable in the dataset in SAS Programming

Add the dataset name as a variable in the dataset

Mishka1 — Tue, 03 Apr 2012 13:39:15 GMT

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!

Add the dataset name as a variable in the dataset

Linlin — Tue, 03 Apr 2012 13:56:41 GMT

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;

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

Mishka1 — Tue, 03 Apr 2012 14:24:33 GMT

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.

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

Linlin — Tue, 03 Apr 2012 14:41:09 GMT

do you have table "DICTIONARY.lib"?

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

art297 — Tue, 03 Apr 2012 14:46:51 GMT

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;

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

Reeza — Tue, 03 Apr 2012 15:00:17 GMT

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

data test;

set sashelp.class sashelp.cars indsname=dset;

source=dset;

run;

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

Linlin — Tue, 03 Apr 2012 17:51:48 GMT

If you don't have sas 9.2, try this one:

libname test "c:\temp";

data test.aa;

input item;

cards;

;

data test.bb;

input item;

cards;

;

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

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

Mishka1 — Tue, 03 Apr 2012 18:08:36 GMT

I should have mentioned: I'm on 9.1

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

Ksharp — Fri, 06 Apr 2012 11:05:21 GMT

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