SAS 转置逗号分隔字段 [英] SAS Transpose Comma Separated Field

问题描述

这是对我之前的一个问题的跟进.转置逗号分隔字段

This is a follow-up to an earlier question of mine. Transposing Comma-delimited field

我针对特定案例得到的答案，但现在我有一个更大的数据集，因此在 datalines 语句中读取它不是一种选择.我有一个类似于此过程创建的数据集:

The answer I got worked for the specific case, but now I have a much larger dataset, so reading it in a datalines statement is not an option. I have a dataset similar to the one created by this process:

data MAIN;
    input ID STATUS STATE $;
cards;
123 7 AL,NC,SC,NY
456 6 AL,NC
789 7 ALL
;
run;

这里有两个问题:1:我需要为 STATE 列中的每个状态单独一行2:注意第三个观察结果是ALL".我需要用一个特定状态的列表来替换它，我可以从一个单独的数据集(下面)中获取它.

There are two problems here: 1: I need a separate row for each state in the STATE column 2: Notice the third observation says 'ALL'. I need to replace that with a list of the specific states, which I can get from a separate dataset (below).

data STATES;
    input STATE $;
cards;
AL
NC
SC
NY
TX
;
run;

所以，这是我正在尝试但似乎不起作用的过程.首先，我创建了一个插补所需的 STATES 列表，以及所述状态的计数.

So, here is the process I am attempting that doesn't seem to be working. First, I create a list of the STATES needed for the imputation, and a count of said states.

proc sql;
    select distinct STATE into :all_states separated by ','
    from STATES;
    select  count(distinct STATE) into :count_states
    from STATES;
quit;

其次，我尝试将ALL"值出现在 STATE 的列表中.这是第一个错误出现的地方.如何确保变量 STATE 对于新值足够长?另外，我如何处理逗号?

Second, I try to impute that list where the 'ALL' value appears for STATE. This is where the first error appears. How can I ensure that the variable STATE is long enough for the new value? Also, how do I handle the commas?

data x_MAIN;
    set MAIN;
    if STATE='ALL' then STATE="&all_states.";
run;

最后，我使用 SCAN 功能一次读取一种状态.我也在这里遇到错误，但我认为修复上述部分可能会解决它.

Finally, I use a SCAN function to read in one state at a time. I'm also getting an error here, but I think fixing the above part may solve it.

data x_MAIN_mod;
    set x_MAIN;
    array state(&count_states.) state:;
    do i=1 to dim(state);
        state(i) = scan(STATE,i,',');
    end; 
run;

预先感谢您的帮助！

推荐答案

看起来您快到了.在最后一个数据步骤中试试这个.

Looks like you are almost there. Try this on the last Data Step.

data x_MAIN_mod;
    set x_MAIN;
    format out_state $2.;
    nstate = countw(state,",");
    do i=1 to nstate;
       out_state = scan(state,i,",");
       output;
    end; 
run;

这篇关于SAS 转置逗号分隔字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！