attach folder name to the data

Reply
Occasional Contributor
Posts: 8

attach folder name to the data

Hello, I'm wondering is there a way to create a new variable in the dataset that's equal to the folder name. For example, I have a folder abc which contains 100 sas dataset. I want to create a new variable for each dataset that's all equal to the folder name abc.

 

 

Thank you very much

 

XB

Super User
Posts: 19,772

Re: attach folder name to the data

You want to add a field to each of the 100 data sets?

Is this for one  library or multiple? How generic of a solution do you require. 

 

SAS will recreate the data sets which can be cumbersome if your datasets are large. 

The field will not be dynamic - if the datasets are moved the old folder name will exist. 

 

Can an you explain the logic for this? Would a macro that took a dataset reference - including libname, and returns the folder be sufficient?

 

 

Occasional Contributor
Posts: 8

Re: attach folder name to the data

Hello, Reeza.

So all my data is in SAS format. And I have a root folder containing 10 subfolders. Within each subfolder, there are around 100 dataset.I basically want to create a new variable that's equal to the name of the subfolder for each of the dataset. I originally plan to use a macro but still can't figure out how to do that.

 

Cheers,

 

XB

Super User
Posts: 19,772

Re: attach folder name to the data

Let me check if I understand the full scope of your issue.

 

You have 10 different folders. Inside each of the 10 folders is approximately 100 files, with files having the same name across the different folders. You want to append each of the 10 files in the 10 folders into one dataset and keep a variable to identify the source dataset. This will result in 100 datasets, which is a combination of all datasets in your original folders.

 

Is each data set in all folders, or do you need that to be dynamic?

Can we assume that the 10 folder names are provided or can you provide an example of the 'rules' of the datasets.

 

You need to use INDSNAME option with a solution of something like the following, except somehow 100 times.

data new.data1;

set lib1.data1
     lib2.data1
    lib3.data1
    ...
    lib10.data1
   INDSNAME=source;

original_file = source;

run;
Occasional Contributor
Posts: 8

Re: attach folder name to the data

Hello, Reeza.
So the data is like this:I got 10 folders. One of them is called 2009Q1 and another one is called 2009Q2...etc. Within each folder, There are around 100 SAS datasets so in total there are around 1000. The task I try to accomplish is for each dataset within each folder, I want to create a variable called date that is equal to the folder's name. This is to identify which folder the dataset comes from. So for example, in the first folder, the first dataset is a. I want to add a new variable date, which is always equal to 2009Q1, to the dataset a. Within the same folder, I want to create this same variable for the dataset b and also the other 98 datasets.

 

 

 

Thank you so much for your help

 

XiaoBing 

Super User
Posts: 19,772

Re: attach folder name to the data

%macro add_dataset(lib_name);

%*get list of datasets in library into macro variables;
proc sql;
create table dlist as 
select name into :dset1-
from sashelp.vtable
where upper(libname)="&lib_name";
quit;

%*get number of datasets to loop over;

%let nobs=&sqlobs;

%*loop through datasets;
%do i=1 %to &nobs;

    data  &lib_name..&&dset&i.;
      set  &lib_name..&&dset&i.;

      date="&lib_name..&&dset&i.";
    run;

%end;

%mend;

This isn't the best way to do this, especially if you do plan to append the datasets later on. However, it's very simple to follow and modify. The code is untested, but should work to get you started.

 

Super Contributor
Posts: 438

Re: attach folder name to the data

If by foldername you mean the physical path of the library (like "/home/users/sasdata/project1"  or "c:\users\user1\sasdata\project1") note the PATHNAME() function:

 

%*loop through datasets;
%do i=1 %to &nobs;

    data  &lib_name..&&dset&i.;
      set  &lib_name..&&dset&i.;
      folder=pathname("&lib_name");
      date="&lib_name..&&dset&i.";
    run;

%end;
Ask a Question
Discussion stats
  • 6 replies
  • 495 views
  • 0 likes
  • 3 in conversation