Help using Base SAS procedures

Parse file name and create variables out of parsing

Reply
Contributor
Posts: 68

Parse file name and create variables out of parsing

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

PROC Star
Posts: 7,360

Re: Parse file name and create variables out of parsing

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.

Super User
Posts: 9,671

Re: Parse file name and create variables out of parsing

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

Ask a Question
Discussion stats
  • 2 replies
  • 140 views
  • 4 likes
  • 3 in conversation