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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.