Creating variables from variable values

[jakshay]

Hi

I have a data set that looks like:

Customer	Var1	Var2	Var3	Var4
1	Value_A	Value_B	Value_C	Value_D
2	Value_E	Value_F	Value_G	Value_H
4	Value_I	Value_J	Value_K	Value_L

I want to create different flags for each customer which take 0 and 1 value for each Value_A,Value_B,Value_C,Value_D,..... ... .. .. .Value_L

Currently I am using "If" conditions to do that but it is very tedious if any value gets added to those variables. I think it can be achieved easily by some of the advanced sas functions.

any help is highly appreciated.

thanks!

[Keith]

Here's one way using 2 arrays.  I've hard coded the new variable list, but you could extract the values from the source table and store them in a macro list.

data have;

input Customer Var1 $ Var2 $ Var3 $ Var4 $;

cards;

1 Value_A Value_B Value_C Value_D

2 Value_E Value_F Value_G Value_H

4 Value_I Value_J Value_K Value_L

;

run;

data want;

set have;

array oldvals{*} Var: ;

array newvals{*} Value_A Value_B Value_C Value_D Value_E Value_F Value_G Value_H Value_I Value_J Value_K Value_L;

do _i=1 to dim(newvals);

do _j=1 to dim(oldvals) until(oldvals{_j}=vname(newvals{_i}));

  newvals{_i}=(oldvals{_j} eq vname(newvals{_i}));

end;

end;

drop _: var: ;

run;

[art297]

Here is a way that uses if then statements, but would be easier (I think) to manage:

data have;

  input Customer (Var1-Var4) ($);

  cards;

1 Value_A Value_B Value_C Value_D

4 Value_I Value_J Value_K Value_L

2 Value_E Value_F Value_G Value_H

5 Value_A Value_B Value_C Value_L

;

proc transpose data=have out=need;

  var Var:;

  by Customer notsorted;

run;

proc sql noprint;

  select distinct "if '"||strip(col1)||"' in Vars then "||

                  strip(col1)||" =1;else "||strip(col1)||" = 0;"

    into :create_vars separated by " "

      from need

  ;

quit;

data want (drop=var:);

  set have;

  array vars(*) var:;

  &create_vars.

run;

[data_null__]

If you don't want to know anything about the values of the VARs this might meet your needs. It assumes that the values of the VARs are also valid "SAS Names".

data a;
   input customer:$1. (var1-var4)($);
   cards;
1 Value_A Value_B Value_C Value_D
2 Value_E Value_F Value_G Value_H
4 Value_I Value_J Value_K Value_L
5 Value_I Value_I Value_K Value_K
;;;;
   run;
proc transpose data=a out=b;
   by customer;
   var var1-var4;
   run;
proc transreg data=b;
   model class(col1 / zero=sum) / cprefix=0;
   output design out=c;
   id customer;
   run;
   quit;
proc summary data=c nway;
   class customer;
   output out=d max(value:)=;
   run;
Proc print;
   run;

I left out an important CLASS option ZERO=SUM.  Without this option VALUE_L is not included.

Message was edited by: data _null_