Parse file name and create variables out of parsing

[Nikos]

Hi all,

I try to import in SAS multiple EXCEL files.

Specifically I import "Named ranges" that reside in various spreadsheets (from various EXCEL files).

Each "named range" has a name like

BLUE_GREEN_WHITE_N_P_1999

BLUE_GREEN_WHITE_N_P_2000

........................

........................

BLUE_RED_WHITE_N_P_1999

BLUE_RED_WHITE_N_P_2000

........................

........................

etc.

Is it possible to "parse" the above names i.e. BLUE, GREEN, WHITE, N, P, 1999 to create:

1.- variables with these names that will be added to the right of the imported "named range" area

2.- populate these variables with their names

            New created variables                               Named range                      

BLUE  GREEN   WHITE  N P   1999        VAR1  VAR2 ..........................VAR20

BLUE   GREEN   WHITE   N   P   1999          4555   555                               566

BLUE   GREEN   WHITE   N   P   1999          4555   555                               566

BLUE   GREEN   WHITE   N   P   1999          4555   555                               566

.........................................................................................................................

I would like to thank you in advance

Best regards

Nikos

[art297]

How do you indicate the named ranges when you import the worksheets?  If you do it with a macro, you could just pass the named range into the macro, and then use the macro variable to both identify it during the import and to create the desired variable.

[Ksharp]

How about:

data _null_;
input file $40.;
call execute(catt('libname x',_n_,' excel "',file,'" getnames=no;'));
cards;
c:\temp\a.xls
c:\temp\b.xls
c:\temp\c.xls
c:\temp\d.xls
;
run;
data _null_;
 set sashelp.vmember(where=(libname like 'X%' and memname like '%_N_P_%')) end=last;
 if _n_ eq 1 then call execute('data want; set ');
 call execute(cats(libname,'."',memname,'"n'));
 if last then call execute("indsname=_dsname;tname=scan(_dsname,-2,'_','kad');run;");
run;

Ksharp