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!
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;
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;
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".
I left out an important CLASS option ZERO=SUM. Without this option VALUE_L is not included.
Message was edited by: data _null_
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.
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.