So assuming you are right and those are the XLSX files of interest they should not be hard to read with SAS because all of the variables appear to be numeric. You just need to start reading in cell A4 and tell SAS to not invent variable names. You can then read the whole sheet and transpose it to get the metadata for the variables.
I tried it for the first two sheets for one of the files and the metadata (column headers) are exactly the same.
filename x "C:\downloads\PRAMS-MCH-Indicators-2016-2021.xlsx";
proc import dbms=xlsx datafile=x out=meta_2016 replace ;
getnames=NO;
range='2016$A1:';
run;
proc import dbms=xlsx datafile=x out=data_2016 replace ;
getnames=NO;
range='2016$A4:';
run;
proc transpose data=meta_2016(obs=3) out=names_2016;
var _all_;
run;
proc import dbms=xlsx datafile=x out=meta_2017 replace ;
getnames=NO;
range='2017$A1:';
run;
proc import dbms=xlsx datafile=x out=data_2017 replace ;
getnames=NO;
range='2017$A4:';
run;
proc transpose data=meta_2017(obs=3) out=names_2017;
var _all_;
run;
proc compare data=names_2016 compare=names_2017;
run;
You can process the headers to generate your own unique variable names.
data newnames_2017 ;
set names_2017 ;
length varname $32 label $256 indicator variable $200 ;
retain indicator variable ;
if _N_=1 then varname='STATE';
else do;
if col1 ne ' ' then do;
ind_num+1;
indicator=col1;
var_num=0;
end;
if col2 ne ' ' then do;
variable=col2;
var_num+1;
sub_num=0;
end;
sub_num+1;
varname=cats('var',ind_num,'_',var_num,'_',sub_num);
label=catx(' - ',indicator,variable,col3);
end;
run;
proc print;
var _name_ varname label ;
run;
So you get something like this:
Which you can use to generate code to RENAME the variables and attach a label.
Or perhaps use to reshape the data into more normal form since it looks like that third level of name is the same for every variable:
... View more