BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Mishka1
Fluorite | Level 6

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Linlin
Lapis Lazuli | Level 10

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

8 REPLIES 8
Linlin
Lapis Lazuli | Level 10

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
Fluorite | Level 6

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 

Linlin
Lapis Lazuli | Level 10

do you have table "DICTIONARY.lib"?    

art297
Opal | Level 21

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
Super User

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
Fluorite | Level 6

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

Linlin
Lapis Lazuli | Level 10

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
Super User
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 11610 views
  • 2 likes
  • 5 in conversation