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
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.
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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.