topic Re: How do I create a new column per dataset when concatenating multiple datasets? in SAS Enterprise Guide

How do I create a new column per dataset when concatenating multiple datasets?

_SASEG_ — Tue, 27 Apr 2021 12:22:25 GMT

Hello!

I am using SAS Enterprise Guide 7.1.

I am concatenating multiple datasets together, however there isn't a date in any of the datasets, only the title. Is there a way to create a new column per dataset that I am concatenating?

My current code without the extra columns is (for visibility I've kept just 3 months that I actually need):

data cues;
set
cue_201603 (keep= id cue5 cue8  where=(ID IN (&c1.)));
cue_201604 (keep= id cue5 cue8  where=(ID IN (&c1.)));
cue_201605 (keep= id cue5 cue8  where=(ID IN (&c1.)));
run;

I was hoping there was a way to create a column called month per dataset and have it output 201603, 201604 and 201605, so that it is all under the same column.

I also only want to select certain columns to speed it up, so would like to keep the keep statement, and also the where statement, to only select the specific ids in a macro variable.

Can this be done?

Thank you!

Re: How do I create a new column per dataset when concatenating multiple datasets?

Tom — Tue, 27 Apr 2021 14:06:44 GMT

Use the INDSNAME= option of the SET statement.

This is used to name a (temporary) variable that will be populated with the name of the dataset that contributed this observation. To add a permanent variable copy or transform the value into another variable. It kind of looks like the names have a month embedded in them.

data cues;
  length dsname $41 ;
  set
    cue_201603 (keep= id cue5 cue8  where=(ID IN (&c1.)))
    cue_201604 (keep= id cue5 cue8  where=(ID IN (&c1.)))
    cue_201605 (keep= id cue5 cue8  where=(ID IN (&c1.)))
    indsname=dsname
  ;
  month = input(scan(dsname,2,'_.'),yymmn6.);
  format month yymmn6. ;
run;

Re: How do I create a new column per dataset when concatenating multiple datasets?

_SASEG_ — Tue, 27 Apr 2021 19:23:33 GMT

Thanks @Tom! Yes, the date is embedded into the file. It worked perfectly!