Conditionally populate all columns based on category

[Tommy1]

Hi SAS community,

I have a dataset that has different categories. For each row it will tell which category the observation belongs to (In this example I am using cat1, cat2, and cat3). Based on what category, I need to pick the variables that correspond to that category. Then I need to put them all into one combined category that has the first to the nth variable. 

data have1;
input id model $ a b c d e;
datalines;
1 cat1 1 2 3 4 5
2 cat2 2 3 4 5 6
3 cat3 3 4 5 6 7
;
run;
%let cat1= a b c; 
%let cat2= b d e;
%let cat3= e;

Now what I want a final product is;

ID model First Second Third

1  cat1     1        2         3

2  cat2      3        5        6

3  cat3    7         

Can anyone help me with how I would do this? If you have any questions about what I am trying to do I am happy to help.

Best,

Tom

[PaigeMiller · Accepted solution]

Let's leave macro variables out, and instead put the information you had in a macro variable into data set variables. Then call in the poorly understood and difficult to pronounce VVALUEX function.

data b;
infile cards missover;
input model $ firstletter $ secondletter $ thirdletter $;
datalines;
cat1 a b c
cat2 b d e
cat3 e
;
run;

/* You may need PROC SORT in here in real life to do the next MERGE */
data both;
    merge have1 b;
    by model;
    first = vvaluex(firstletter);
    second = vvaluex (secondletter);
    third = vvaluex(thirdletter);
run;

[Tommy1]

Hi Paige, thanks for the reply!  I have never heard about that function and that is really interesting.

I am a little confused how to do this still. When I first expressed the question I don't think I represented my problem as well as I would have liked and I apologize for wasting your time. I thought that I could simplify the problem to figure it out, but it left me more confused.

In my data there are actually two data sets. the first data set has responses to questions. In the example that I provided, the responses are the variables represented by a-e. In the second dataset, each row has a different model denoted by model in the example. Each model corresponds to a separate list of questions (ex. cat1 in dataset two may correspond to questions a, b, and c in dataset one). In my example I tried combining the steps. Using the method that you suggested, I was able to create fields Val1-Val8 in dataset2 that represent the questions that are needed for each category. So if I were to continue my example my data is actually better represented by the example below. My goal is to get the answers from data1 to data2 based on the corresponding model

data data1;
input id mon year a b c d e;
datalines;
1 1 2009 1 2 3 4 5
2 1 2009 2 3 4 5 6
3 1 2009 3 4 5 6 7
;
run;

data data2;
input id mon year model $  val1 $ val2 $ val3 $ val4 $ val5 $; 
datalines;
1 1 2009 cat1 a  c  e
2 1 2009 cat2 b  d  e
3 1 2009 cat3 e
;
run;

  What I need is to match up based on id and monthend and get a final dataset that would look like

id mon year model  val1  val2  val3  val4  val5 q1 q2 q3 q4 q5

1    1   2009 cat1        a       c        e                          1   3    5
2    1    2009 cat2       b      d         e                          3  5    6
3    1   2009 cat3       e                                             7

Thanks for the help,

Tom

[andreas_lds]

The role of the variable "model" is not clear. It seems that you need to merge by id to solve the issue:

data data2;
infile datalines missover; /* added missover option */
input id mon year model $ val1 $ val2 $ val3 $ val4 $ val5 $; 
datalines;
1 1 2009 cat1 a  c  e
2 1 2009 cat2 b  d  e
3 1 2009 cat3 e
;
run;

data work.want;
    merge work.data1 work.data2;
    by id;
    
    length q1-q5 8;
    
    array values[5] val1-val5;
    array questions[5] q1-q5;
    
    do _i = 1 to 5;
        if not missing(values[_i]) then do;
            questions[_i] = vvaluex(values[_i]);
        end;
    end;
    
    drop _i a b c d e;
run;