topic Parse file name and create variables out of parsing in SAS Procedures

Parse file name and create variables out of parsing

Nikos — Tue, 06 Mar 2012 23:14:40 GMT

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

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

I would like to thank you in advance

Best regards

Nikos

Re: Parse file name and create variables out of parsing

art297 — Tue, 06 Mar 2012 23:18:44 GMT

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.

Re: Parse file name and create variables out of parsing

Ksharp — Wed, 07 Mar 2012 03:49:32 GMT

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