topic Re: Insert string variable in datasets in SAS Programming

Insert string variable in datasets

problems99 — Wed, 10 Jun 2015 14:41:23 GMT

Hello,

I have a lot of forecasting series (+/- 50), each one in one table. The list of these tables is reported in the variable code which contains i items. I would like to add a new column name with the name of the file for each row of each file. (The next step is to merge some rows of some tables and to sort them without losing the origin).

However, I don't know how I can do that. The red line is my problem. For constant variable, I write that: name='forecast_01'; but here it doesn't seem to be recognized as a string. I tried different things. When it works it creates either an empty column labelled "name" or an empty column labelled with the name of the table but never a column "name" with the name of the table as value for each row as for name='forecast_01';

%macro add_name;

%do i=1 %to &end

data &&code&i;
set &&code&i;
name=%string(&&code&i);
run;

%end;

%mend add_name;

Thanks for your help!

Re: Insert string variable in datasets

Astounding — Wed, 10 Jun 2015 15:07:11 GMT

You'll need to use double quotes. Single quotes suppress all macro activity:

name = "&&code&i";

Remember macro language isn't changing your data. Macro language is generating the SAS language statements that process the data. You need to be able to picture what the SAS program should look like.

Good luck.

Re: Insert string variable in datasets

RW9 — Wed, 10 Jun 2015 15:14:03 GMT

Well, several methods to get there. If you already have the list of files:

data _null_;

set list_of_files;

call execute('data '||strip(filename)||'; set '||strip(filename)||'; name="'||strip(filename)||'"; run;');

run;

Or;

%macro Add_Name (code=);

data &code.;

set &code.;

name="&code.";

run;

%mend Add_name;

Then call it each element. As I always say, it is easier, and simpler coding to keep you lists in a dataset and generate the code from there. As you are finding with the whole &&code&i. bit, its just not intuitive.

A further way is to use in:

data forecast1 forecast2 ...;

set forecast1 (in=a) forecast2 (in=b)...;

length name $200;

if a then do;

name="forecast1";

output forecast1;

end;

...

Again, the above can be generated based on &i. and call execute. If all the datasets are the same, then I would suggest setting them altogether and then doing further processing on them.

Re: Insert string variable in datasets

ballardw — Wed, 10 Jun 2015 15:19:56 GMT

The option INDSNAME on the set statement will help add a variable to with the set name:

data &&code&i;

set &&code&I INDSNAME=dsn;

Name=dsn;

run;

The DSN is a temporary variable unless assigned to a specific variable during the data set.

IF the data sets all have the same structure you can combine the date sets AND get the name at one time:

Datat newest;

set <list of dataset names> indsname=dsn;

name=dsn;

run;

And if the input data sets have a similar stem of the name such as Forecast you could use a list shortcut:

set forecast: indsname=dsn;

name=dsn;

Re: Insert string variable in datasets

problems99 — Thu, 11 Jun 2015 08:23:25 GMT

Tanks everyone!