topic Re: How to create new columns using data/columns from existing table in SAS Programming

How to create new columns using data/columns from existing table

sufiya — Sat, 03 Feb 2018 19:53:30 GMT

Hello,

Hoping someone can provide me the steps/how to create new columns in SAS Proc SQL. An example would be using the existing data from table 'item.track' - from this table want to create 3 new columns by month, by using the dates that exist in column "Date_Bought" and insert the new columns with data from column "Bought".

Please, can you help me understand, what syntax/logic would need to be used to get the desired end result?

thank you!

Table: item.track

Book	Start_DATE	Item Value	Date_Bought	Bought
ItemA	01-Oct-17	13	01-Nov-17	9
ItemB	01-Oct-17	14	01-Oct-17	14
ItemD	01-Oct-17	15	01-Nov-17	12
ItemE	01-Oct-17	20	01-Nov-17	13
ItemF	01-Oct-17	12	01-Oct-17	14
ItemG	01-Oct-17	14	01-Dec-16	15
ItemH	01-Oct-17	14	01-Oct-17	16
ItemI	01-Oct-17	15	01-Oct-17	17
ItemJ	01-Oct-17	18	01-Dec-16	18
ItemK	01-Oct-17	25	01-Nov-17	19
ItemL	01-Oct-17	13	01-Dec-16	20

End Result- want to achieve this:

Book	Start_DATE	Item Value	Date_Bought_October	Date_Bought_November	Date_Bought_December
ItemA	01-Oct-17	13	0	9	0
ItemB	01-Oct-17	14	14	0	0
ItemD	01-Oct-17	15	0	12	0
ItemE	01-Oct-17	20	0	13	0
ItemF	01-Oct-17	12	14	0	0
ItemG	01-Oct-17	14	0	0	15
ItemH	01-Oct-17	14	16	0	0
ItemI	01-Oct-17	15	17	0	0
ItemJ	01-Oct-17	18	0	0	18
ItemK	01-Oct-17	25	0	19	0
ItemL	01-Oct-17	13	0	0	20

Re: How to create new columns using data/columns from existing table

Reeza — Sat, 03 Feb 2018 20:30:51 GMT

A PROC SQL approach to this question is not an efficient solution. Is this an actual work type problem or a homework problem where you're required to use PROC SQL. If it's a work question, use PROC TRANSPOSE instead. If it's homework, show what you've tried first.

Re: How to create new columns using data/columns from existing table

sufiya — Sat, 03 Feb 2018 21:02:19 GMT

not homework or work - I am trying to learn SAS on my own. I know the SAS proc SQL basics and want to learn more. Sorry thought I included my logic in the original post - see below.

Table: item.track

Book	Start_DATE	Item Value	Date_Bought	Bought
ItemA	01-Oct-17	13	01-Nov-17	9
ItemB	01-Oct-17	14	01-Oct-17	14
ItemD	01-Oct-17	15	01-Nov-17	12
ItemE	01-Oct-17	20	01-Nov-17	13
ItemF	01-Oct-17	12	01-Oct-17	14
ItemG	01-Oct-17	14	01-Dec-16	15
ItemH	01-Oct-17	14	01-Oct-17	16
ItemI	01-Oct-17	15	01-Oct-17	17
ItemJ	01-Oct-17	18	01-Dec-16	18
ItemK	01-Oct-17	25	01-Nov-17	19
ItemL	01-Oct-17	13	01-Dec-16	20

October Bought Column:

proc sql;
create table new format as
select Book, Start_Date, Item_Value, 
case Date_Bought when Date_Bought contains '01Oct2017' then Bought end as Date_Bought_October
from item.track;
quit;

End Result- want to achieve this:

Book	Start_DATE	Item Value	Date_Bought_October	Date_Bought_November	Date_Bought_December
ItemA	01-Oct-17	13	0	9	0
ItemB	01-Oct-17	14	14	0	0
ItemD	01-Oct-17	15	0	12	0
ItemE	01-Oct-17	20	0	13	0
ItemF	01-Oct-17	12	14	0	0
ItemG	01-Oct-17	14	0	0	15
ItemH	01-Oct-17	14	16	0	0
ItemI	01-Oct-17	15	17	0	0
ItemJ	01-Oct-17	18	0	0	18
ItemK	01-Oct-17	25	0	19	0
ItemL	01-Oct-17	13	0	0	20

Re: How to create new columns using data/columns from existing table

Reeza — Sat, 03 Feb 2018 21:30:00 GMT

Then try a PROC TRANSPOSE instead.

@sufiya wrote:

not homework or work - I am trying to learn SAS on my own. I know the SAS proc SQL basics and want to learn more. Sorry thought I included my logic in the original post - see below.

Table: item.track

Book Start_DATE Item Value Date_Bought Bought

ItemA 01-Oct-17 13 01-Nov-17 9

ItemB 01-Oct-17 14 01-Oct-17 14

ItemD 01-Oct-17 15 01-Nov-17 12

ItemE 01-Oct-17 20 01-Nov-17 13

ItemF 01-Oct-17 12 01-Oct-17 14

ItemG 01-Oct-17 14 01-Dec-16 15

ItemH 01-Oct-17 14 01-Oct-17 16

ItemI 01-Oct-17 15 01-Oct-17 17

ItemJ 01-Oct-17 18 01-Dec-16 18

ItemK 01-Oct-17 25 01-Nov-17 19

ItemL 01-Oct-17 13 01-Dec-16 20

October Bought Column:
proc sql;
create table new format as
select Book, Start_Date, Item_Value, 
case Date_Bought when Date_Bought contains '01Oct2017' then Bought end as Date_Bought_October
from item.track;
quit;
End Result- want to achieve this:

Book Start_DATE Item Value Date_Bought_October Date_Bought_November Date_Bought_December

ItemA 01-Oct-17 13 0 9 0

ItemB 01-Oct-17 14 14 0 0

ItemD 01-Oct-17 15 0 12 0

ItemE 01-Oct-17 20 0 13 0

ItemF 01-Oct-17 12 14 0 0

ItemG 01-Oct-17 14 0 0 15

ItemH 01-Oct-17 14 16 0 0

ItemI 01-Oct-17 15 17 0 0

ItemJ 01-Oct-17 18 0 0 18

ItemK 01-Oct-17 25 0 19 0

ItemL 01-Oct-17 13 0 0 20

Re: How to create new columns using data/columns from existing table

sufiya — Sat, 03 Feb 2018 21:44:34 GMT

not familiar at all with SAS Proc Transpose, will read up on it.

Thank you again for your help! 🙂

Re: How to create new columns using data/columns from existing table

art297 — Sat, 03 Feb 2018 22:38:08 GMT

You can do it using SQL, but would take less code using a datastep. You were close (other than one of your variable names contained a space). The following would work:

data track;
  infile datalines dlm='09'x;
  informat Start_Date Date_Bought date11.;
  format Start_Date Date_Bought date11.;
  input Book $	Start_DATE	Item_Value	Date_Bought	Bought;
  cards;
ItemA	01-Oct-17	13	01-Nov-17	9
ItemB	01-Oct-17	14	01-Oct-17	14
ItemD	01-Oct-17	15	01-Nov-17	12
ItemE	01-Oct-17	20	01-Nov-17	13
ItemF	01-Oct-17	12	01-Oct-17	14
ItemG	01-Oct-17	14	01-Dec-16	15
ItemH	01-Oct-17	14	01-Oct-17	16
ItemI	01-Oct-17	15	01-Oct-17	17
ItemJ	01-Oct-17	18	01-Dec-16	18
ItemK	01-Oct-17	25	01-Nov-17	19
ItemL	01-Oct-17	13	01-Dec-16	20
;

proc sql;
  create table new as
    select Book, Start_Date, Item_Value, 
        case month(Date_Bought) when 10 then Bought
        end as Date_Bought_October,
        case month(Date_Bought) when 11 then Bought
        end as Date_Bought_November,
        case month(Date_Bought) when 12 then Bought
        end as Date_Bought_December
      from track
  ;
quit;

Art, CEO, AnalystFinder.com

Re: How to create new columns using data/columns from existing table

sufiya — Sat, 03 Feb 2018 23:02:40 GMT

Thank you art297!! 🙂 it worked

Re: How to create new columns using data/columns from existing table

sufiya — Sat, 03 Feb 2018 23:11:22 GMT

question, if I wanted to combine 2 months together like October & November - is that possible? using the Case.

thank you again!

case month(Date_Bought) when 10 and 11 then Bought
        end as Date_Bought_OctDec

Re: How to create new columns using data/columns from existing table

sufiya — Sat, 03 Feb 2018 23:12:14 GMT

question, if I wanted to combine 2 months together like October & November - is that possible? using the Case.

thank you again!

case month(Date_Bought) when 10 and 11 then Bought
        end as Date_Bought_OctDec

Re: How to create new columns using data/columns from existing table

art297 — Sat, 03 Feb 2018 23:17:06 GMT

Yes you can, but there are a number of ways to do it. Here is one.:

proc sql;
  create table new as
    select Book, Start_Date, Item_Value, 
        case month(Date_Bought)
          when 10 then Bought
          when 11 then Bought
        end as Date_Bought_October_November,
        case month(Date_Bought) when 12 then Bought
        end as Date_Bought_December
      from track
  ;
quit;

Art, CEO, AnalystFinder.com

Re: How to create new columns using data/columns from existing table

sufiya — Sat, 03 Feb 2018 23:27:07 GMT

when I add your logic receive an error message: ERROR 78-322 Expecting a ','.

    case month(Date_Bought)
          when 10 then Bought
          when 11 then Bought
        end as Date_Bought_October_November,

Re: How to create new columns using data/columns from existing table

art297 — Sat, 03 Feb 2018 23:30:45 GMT

Show the entire proc sql code that you ran. Your code, unlike mine, ended with a semi-colon, but your error was that it was EXPECTING a semi-colon. Did you include a semi-colon before the case statement AND was there something else you were selecting after it?

Art, CEO, AnalystFinder.com

Re: How to create new columns using data/columns from existing table

sufiya — Sat, 03 Feb 2018 23:34:31 GMT

I only get the error when I do

        case month(Date_Bought)
          when 10 then Bought
          when 11 then Bought
        end as Date_Bought_October_November,

the full code is from the logic you provided:

proc sql;
  create table new as
    select Book, Start_Date, Item_Value, 
  
     case month(Date_Bought) when 10 then Bought
        end as Date_Bought_October, 
     case month(Date_Bought) when 11 then Bought
        end as Date_Bought_November,   
      case month(Date_Bought)
          when 10 then Bought
          when 11 then Bought
        end as Date_Bought_October_November,
  case month(Date_Bought) when 12 then Bought
        end as Date_Bought_December
      from track
  ;
quit;

Re: How to create new columns using data/columns from existing table

art297 — Sat, 03 Feb 2018 23:42:55 GMT

Now I think it's easier to use a datastep. e.g.:

data new;
  set track;
  if month(Date_Bought) in (10,11) then do;
    Date_Bought_October_November=Bought;
    if month(Date_Bought) eq 10 then Date_Bought_October=Bought;
    else if month(Date_Bought) eq 11 then Date_Bought_November=Bought;
  end;
  else if month(Date_Bought) eq 12 then Date_Bought_December=Bought;
run;

Art, CEO, AnalystFinder.com

Re: How to create new columns using data/columns from existing table

art297 — Sat, 03 Feb 2018 23:51:55 GMT

Conversely, I don't get the error you mentioned when I submit:

proc sql;
  create table new as
    select Book, Start_Date, Item_Value, 
  
     case month(Date_Bought) when 10 then Bought
        end as Date_Bought_October, 
     case month(Date_Bought) when 11 then Bought
        end as Date_Bought_November,   
     case month(Date_Bought)
          when 10 then Bought
          when 11 then Bought
        end as Date_Bought_October_November,
  case month(Date_Bought) when 12 then Bought
        end as Date_Bought_December
      from track
  ;
quit;

Art, CEO, AnalystFinder.com

Re: How to create new columns using data/columns from existing table

sufiya — Sun, 04 Feb 2018 00:12:24 GMT

ok, so tried it again and no errors this time @_@

Thank you again @art297!!

Re: How to create new columns using data/columns from existing table

Tom — Sun, 04 Feb 2018 02:28:01 GMT

@sufiya wrote:

question, if I wanted to combine 2 months together like October & November - is that possible? using the Case.

thank you again!
case month(Date_Bought) when 10 and 11 then Bought
        end as Date_Bought_OctDec

You can use a CASE statement, but not in that way. That statement is looking for data bought in January, not October or December. '10 and 11' is an boolean expression. Since both 10 and 11 are not zero then it is always true so it has a value of 1.

You can either use two WHEN clauses

case month(Date_Bought)
 when 10 then Bought
 when 12 then Bought
end as Date_Bought_OctDec

Or put the test in the WHEN clause.

case when month(Date_Bought) in (10,11) then Bought
end as Date_Bought_OctDec

Re: How to create new columns using data/columns from existing table

sufiya — Mon, 05 Feb 2018 00:43:59 GMT

thank you @Tom that worked too!

Question, what logic is needed to insert zero '0' into the blank rows/variables?

tried adding the following to CASE,

else if column=. then column=0 end as October

but receiving an error. please help ....

Re: How to create new columns using data/columns from existing table

Reeza — Mon, 05 Feb 2018 00:48:56 GMT

IF isn't valid in SQL, try replacing it with WHEN instead.

Re: How to create new columns using data/columns from existing table

sufiya — Mon, 05 Feb 2018 00:51:58 GMT

Thank you! worked with when and not if