Excel数据导入SAS并将矩阵格式表格转换为结构化表格问题求助

现有代码的核心问题 #

• 州缩写截断问题：你在data have步骤中虽然定义了state长度为$2，但未对赋值触发逻辑做行过滤，非州名行的短字符内容也被赋值给了state，同时导入的col1默认长度可能被短值截断，最终导致州缩写只保留单个字母。
• 年份取值错误：你apple数据集取的是have(obs=2 drop=a)的转置结果，错误将非年份行的内容识别为年份，最终输出的年份为字母而非数字。
• 数据分散多列：最后一步proc transpose使用id id参数，把每个不同的指标名生成了单独的列，和你需要的「指标名单独一列、对应数值单独一列」的目标格式逻辑完全相反。

修正后的完整代码 #

/* 第一步：导入Excel，关闭自动读取表头 */
proc import datafile='C:\Users\jjsin\Desktop\PhD\STCR.xlsx' dbms=xlsx out=raw replace;
getnames=no;
run;

/* 第二步：提前确认原始导入数据行结构，可选运行调试
proc print data=raw(obs=5); run;
*/

/* 提取年份行：假设年份在第2行，可根据实际行号调整firstobs值 */
proc transpose data=raw(obs=1 firstobs=2 drop=a) out=year_list(drop=_name_ rename=(col1=year));
var _all_;
run;

/* 提取指标名行：假设指标名在第1行 */
proc transpose data=raw(obs=1 drop=a) out=id_list(drop=_name_ rename=(col1=id));
var _all_;
run;

/* 拼接列名和指标、年份的对应关系 */
data col_map;
    merge id_list year_list;
    length col_name $32;
    col_name = cats("COL", _n_+1); /* A列为COL1，数据列从COL2开始 */
run;

/* 第三步：给每行补全正确的州缩写 */
data raw_with_state;
    set raw;
    length state $2;
    retain state;
    /* 仅A列是2位州缩写时才更新state，避免错误赋值 */
    if not missing(COL1) and length(strip(COL1))=2 then state=strip(COL1);
    /* 跳过前2行表头，根据实际表头行数调整_n_阈值 */
    if _n_ >=3;
run;

/* 第四步：转置为长表 */
proc transpose data=raw_with_state out=long(drop=_name_ rename=(col1=value));
    by state notsorted;
    var COL2-COL25; /* 对应24年的24列数据，可根据实际列数调整范围 */
run;

/* 第五步：匹配指标、年份，生成最终结果 */
proc sql;
    create table want as
    select a.state, b.year, b.id, a.value
    from long a left join col_map b
    on upcase(a._NAME_) = upcase(b.col_name)
    order by year, state, id;
quit;

调试提示 #

如果仍出现取值错误，先运行proc print data=raw(obs=10); run;确认原始导入数据的结构：

• 年份所在行号调整proc transpose提取年份时的firstobs=参数
• 州缩写开始的行号调整raw_with_state步骤中if _n_ >=的阈值
• 数据列范围调整var COL2-COLxx的结束列号

内容的提问来源于stack exchange，提问作者J.J. Singh