DATA Step, Macro, Functions and more

Creating variables from variable values

Reply
N/A
Posts: 1

Creating variables from variable values

Hi

I have a data set that looks like:

CustomerVar1Var2Var3Var4
1Value_AValue_BValue_CValue_D
2Value_EValue_FValue_GValue_H
4Value_IValue_JValue_KValue_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!

Regular Contributor
Posts: 151

Re: Creating variables from variable values

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;

PROC Star
Posts: 7,490

Re: Creating variables from variable values

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=varSmiley Happy;

  set have;

  array vars(*) var:;

  &create_vars.

run;

Respected Advisor
Posts: 3,799

Re: Creating variables from variable values

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(valueSmiley Happy=;
   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_

Ask a Question
Discussion stats
  • 3 replies
  • 241 views
  • 0 likes
  • 4 in conversation