SAS/SQL - Is it possible to match data points between columns and create a new column with data entr-CodePudding

Fairly new to SAS/SQL, and have a query, I've not been able to solve. I apologies if the details of my problems are a bit vague, but due to my job I can't be too detailed about the actual data, or show the actual code I have.

I have a table that's a combination of sales data and backlog/catalogue type data. Within the sales data is a variable that informs which group the sale belongs to. Below is a table that illustrates my data and my intent, i.e creating the Group variable that states which group the sale belongs to. Is there a way within SAS to match the catalogue_code to the group columns and return a new variable that is the column header of the matched column (ideally without the underscore).

Hope that's enough info for someone to point me in the right direction.

catalogue_code	Group_A	Group_B	Group_C	Group
B01235	B01234	B01235	B01236	Group B
B01234	B01234	B01235	B01236	Group A
B01235	B01234	B01235	B01236	Group B
B01236	B01234	B01235	B01236	Group C
B01235	B01234	B01235	B01236	Group B

CodePudding user response：

Welcome to the commutity :-) Here is a generic solution.

data have;
input catalogue_code $ Group_A $ Group_B $ Group_C $;
datalines;
B01235 B01234 B01235 B01236 
B01234 B01234 B01235 B01236 
B01235 B01234 B01235 B01236 
B01236 B01234 B01235 B01236 
B01235 B01234 B01235 B01236 
;

data want(drop = idx);
   set have;
   array g{*} Group:;
   idx = whichc(catalogue_code, of g[*]);
   group = tranwrd(vname(g[idx]), '_', ' ');
run;

Result:

catalogue_code Group_A Group_B Group_C group
B01235         B01234  B01235  B01236  Group B
B01234         B01234  B01235  B01236  Group A
B01235         B01234  B01235  B01236  Group B
B01236         B01234  B01235  B01236  Group C
B01235         B01234  B01235  B01236  Group B

CodePudding user response：

It would definitely help if you give more details. And do you really have a table that has multiple columns that each contains same value in every row? That is kinda pointless...

If there are only a few groups that wont change you can use a simple case statement:

proc sql;
    create table want as
        select catalogue_code, 
        case
            when catalogue_code = 'B01234' then 'Group A'
            when catalogue_code = 'B01235' then 'Group B'
            when catalogue_code = 'B01236' then 'Group C'
            else ''
        end as Group 
            from have
    ;
quit;

If you have many groups, you can join groups to your have table:

data groups;
    input catalogue_code $ group $;
    infile datalines4 dlm='|' truncover;
    datalines;
B01234|Group A
B01235|Group B
B01236|Group C
;
run;

proc sql;
    create table want as
        select a.catalogue_code, b.group
            from have a
            join groups b on a.catalogue_code=b.catalogue_code
    ;
quit;

As PeterClemmensen has shown, you can also use arrays. You could also use hashs. Without knowing your data it's hard to say which aproach is the best.