DATA Step, Macro, Functions and more

Creating new columns based on contents of a previous column

Reply
Contributor
Posts: 28

Creating new columns based on contents of a previous column

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

Valued Guide
Posts: 858

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

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

Contributor
Posts: 28

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

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

Valued Guide
Posts: 858

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

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.

Super User
Posts: 6,962

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

This may be a fit for proc transpose.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,413

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

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;

    Super User
    Posts: 9,687

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

    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

    Contributor
    Posts: 28

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

    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

    Ask a Question
    Discussion stats
    • 7 replies
    • 329 views
    • 0 likes
    • 5 in conversation