topic Re: Creating new columns based on contents of a previous column in SAS Programming

Creating new columns based on contents of a previous column

troopon — Thu, 30 Apr 2015 11:18:24 GMT

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

Re: Creating new columns based on contents of a previous column

Steelers_In_DC — Thu, 30 Apr 2015 11:32:03 GMT

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

Re: Creating new columns based on contents of a previous column

Kurt_Bremser — Thu, 30 Apr 2015 11:58:12 GMT

This may be a fit for proc transpose.

Re: Creating new columns based on contents of a previous column

RW9 — Thu, 30 Apr 2015 12:05:58 GMT

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;

Re: Creating new columns based on contents of a previous column

troopon — Thu, 30 Apr 2015 12:37:46 GMT

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

Re: Creating new columns based on contents of a previous column

Steelers_In_DC — Thu, 30 Apr 2015 12:54:39 GMT

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.

Re: Creating new columns based on contents of a previous column

Ksharp — Thu, 30 Apr 2015 14:56:04 GMT

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

Re: Creating new columns based on contents of a previous column

troopon — Thu, 30 Apr 2015 15:04:30 GMT

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