CODEWITHSUNDEEP
loopsdatemacrossasappend
EmShing
EmShing

Reputation: 29

How to create date field using the date from data filename in SAS?

How do I get the dates from the file name to populate the date column?

I have 23 data files:

price_20070131
price_20070228
price_20070331
.
.
.
price_20081130

In the data file, price_20070131, it currently looks like this:

ID     Product    
001       A    
002       B      
003       C

I want my output to look like this:

ID     Product     Date
001       A      31Jan2007
002       B      31Jan2007 
003       C      31Jan2007

The same will be repeated to all the 23 data files. And final result would merge all 23 files to look like this:

ID     Product     Date
001       A      31Jan2007
002       B      31Jan2007 
003       C      31Jan2007
001       A      28Feb2007
002       B      28Feb2007 
003       C      28Feb2007
.
.
.
.
001       A      30Nov2007
002       B      30Nov2007 
003       C      30Nov2007

Upvotes: 0

Views: 848

Answers (1)

Reeza
Reeza

Reputation: 21274

Use the INDSNAME option to add the file name and then use SCAN/SUBSTR() to extract the date portion. This would append all data sets starting with price_2007 and price_2008 and add a date field.

 data want;
    set price_2007: price_2008: indsname=source;
    date=input(scan(source, 2, '_'), yymmdd10.);
    format date date9.;
 run;

EDIT: SAS 9.1 is about 15 years old so you should really upgrade. Upgrades are included with your license. This means you don't have data set lists or the ability to use the INDSNAME option and means you either need a macro solution of some sort. 4 lines of code becomes 47...

Making an assumption that your data sets are named PRICE_LAST_DAY_MONTH consistently. 

*sample data sets for demonstration;
data price_20080131;
set sashelp.class;
test=1;
run;

data price_20080229;
set sashelp.class;
test=2;
run;

%macro stack_data_add_date(start_date=, end_date=, outData=, debug=);

    %*get parameters for looping, primarily the number of intervals;
    data _null_;
        start_date= input("&start_date", yymmdd10.);
        end_date = input("&end_date", yymmdd10.);
        n_intervals = intck('month', start_date, end_date);

        call symputx('start_date', start_date, 'l');
        call symputx('end_date', end_date, 'l');
        call symputx('n_intervals', n_intervals, 'l');
    run;

    %*loop from 0 - starting time to end;
    %do i=0 %to &n_intervals;

    %*determine end of month date for dataset name;
    %let date = %sysfunc(intnx(month, &start_date, &i., e));

    %*output statistics for testing;
    %if &debug=Y %then %do;
        %put &n_intervals;
        %put &start_date;
        %put &end_date;
    %end;

    %*create a view with the data and date added in;
    data _temp / view=_temp;
    set price_%sysfunc(putn(&date, yymmddn8.));
    date = &date.;
    format date date9.;
    run;

    %*insert into master table;
    proc append base=&outData data=_temp;
    run;

    %*delete view so it doesn't exist for next loop;
    proc datasets lib=work nodetails nolist;
    delete _temp / memtype=view;
    run;quit;

%end;


%mend;

*test;
%stack_data_add_date(start_date=20080131, end_date=20080229, outData=want, debug=Y);

Upvotes: 1

Related Questions