BookmarkSubscribeRSS Feed
jakshay
Calcite | Level 5

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!

3 REPLIES 3
Keith
Obsidian | Level 7

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
Opal | Level 21

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__
Jade | Level 19

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_

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 831 views
  • 0 likes
  • 4 in conversation