Creating new columns based on contents of a previous column

[troopon]

Hi

I am looking to be able to create additional columns in a SAS data set which are populated with a 1 or 0 based on whether

the entry in a previous column.

eg if I wanted to create 1 additional column I would do this

data test1;

set base_data;

if column1 = 'entry1' then entry1 = 1; else entry1 = 0;

run;

and if i wanted to create 2 additional columns I would do it similarly.

data test1;

set base_data;

if column1 = 'entry1' then entry1 = 1; else entry1 = 0;

if column1 = 'entry2' then entry2 = 1; else entry2 = 0;

run;

However I have a lot of distinct entries that could be in the column and I don't want to have to create loads of if else statements.

I am wondering if it's possible to create a sinlge if else statement using macros variables that basically says if

if column1 = x then x = 1 else x = 0 for all x that exist. Note all x are text fields

It sounds simple on the face of it but I have been unable to figure out something which works.

Thanks

Stephen

[Steelers_In_DC]

can you give an example of the type of variables you are talking about?  Maybe a few lines of the before dataset?

[troopon]

it's a sales type table so the table looks like this. There are lots more fields in the original dataset but not anything important

Customer number   Product Code      Amount

C123456                   C1                    £100

C123457                   F2                    £200

C127890                   P3                    £150

and I want to create

Customer number   Product Code      Amount         C1        C2     F1      F2    F3   P2   P3

C123456                   C1                    £100            1           0       0       0      0      0     0

C123457                   F2                    £200            0           0       0       1      0      0     0

C127890                   P3                    £150            0           0       0       0      0      0     1

but I don't want to write an if statement for every product code like my original post has.

This is only a few lines so obviously not all codes covered. There is only as many columns extra as distinct codes

[Steelers_In_DC]

This may or may not suit your needs, take a look at this:

data have;

infile cards dsd;

input Customer_number $ Product_Code $  Amount;

cards;

C123456,C1,100

C123457,F2,200

C127890,P3,150

;

run;

proc transpose data=have out=want(drop=_name_);by customer_number product_code amount;id product_code;var product_code;

Depending on what this will be used for you might want to think about proc format too.

[Kurt_Bremser]

This may be a fit for proc transpose.

[RW9]

Simple, two quick options I see, though just guessing here as you provide no test data/required output:

1) Arrays:

data want;

     set have;

     array elements

entry1-entry10;

     do i=1 to 10;

          if elements=column then elements="1";

     end;

run;

2) create a dataset with distinct values:

proc sql;

     create table LOOP as

     select     distinct COLUMN

     from        HAVE;

quit;

Then use this to generate your code:

data _null_;

     set LOOP end=last;

     if _n_=1 then call execute('data want; set have;');

     call execute(' if column=entry'||strip(put(_n_,best.))|' then ...');

     if last then call execute('run;');

run;

[Ksharp]

data have;
infile cards dsd;
input Customer_number $ Product_Code $  Amount;
cards;
C123456,C1,100
C123457,F2,200
C127890,P3,150
;
run;
proc sql noprint;
 select distinct Product_Code into : list separated by ' ' from have;
quit;
data want;
 set have;
 array x{*} &list ;
 do i=1 to dim(x);
  if Product_Code=vname(x{i}) then x{i}=1;
   else x{i}=0;
 end;
 drop i;
run;

Xia Keshan

[troopon]

This is great thanks.

I am a bit of a SAS novice so would not have ever been able to come up with that. Not entirely sure I understand exactly how it works but I'll work through it.

Stephen