add counter of values for only 1 record in sql/sas

Question

Hello every one i have a table with only 1 record i want to count the number of times a particular value is there (counter) eg in table 1 we have 3 goals in table2 we have from goal1 to goal 3 ,similarly for current state

data have;
    length var1-var7 $15.;
    input var1$ var2$ var3$ var4$ var5$ var6$ var7$;
    datalines;
client goal current_State goal current_State goal current_State
;
run;

What I am looking for

Answer 1

This code will calculate it using by-group processing, so you don't need to worry about keeping track of the unique words within each column. First, transpose it to a long format and sort it by word:

proc transpose data=have out=have_tpose;
    var var1-var7;
run;

proc sort data=have_tpose;
    by col1;
run;

_NAME_  COL1
var1    client
var3    current_State
var5    current_State
var7    current_State
var2    goal
var4    goal
var6    goal

Now we can use by-group processing to add a numbered suffix to each word.

data add_suffix;
    set have_tpose;
    by col1;

    if(first.col1) then suffix=0;

    suffix+1;

    col1  = cats(col1, '_', suffix);
    order = input(substr(col1, 4), 8.);
run;

_NAME_  COL1             suffix   order
var1    client_1         1        1
var3    current_State_1  1        3
var5    current_State_2  2        5
var7    current_State_3  3        7
var2    goal_1           1        2
var4    goal_2           2        4
var6    goal_3           3        6 

Now, we just need to resort it by order to get it back into the original order, then transpose it back to the desired format.

proc sort data=add_suffix;
    by order;
run;

proc transpose data=add_suffix out=want(drop=_NAME_);
    id _NAME_;
    var COL1;
run;

var1        var2    var3            var4    var5           var6     var7
client_1    goal_1  current_State_1 goal_2  current_State_2 goal_3  current_State_3