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

sufiya · Posted 02-03-2018 02:53 PM

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

art297 · Posted 02-03-2018 05:38 PM

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

View solution in original post

Reeza · Posted 02-03-2018 03:30 PM

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.

sufiya · Posted 02-03-2018 04:02 PM

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

Reeza · Posted 02-03-2018 04:30 PM

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

sufiya · Posted 02-03-2018 04:44 PM

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

Thank you again for your help! 🙂

art297 · Posted 02-03-2018 05:38 PM

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

sufiya · Posted 02-03-2018 06:02 PM

Thank you art297!! 🙂 it worked

sufiya · Posted 02-03-2018 06:11 PM

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

art297 · Posted 02-03-2018 06:17 PM

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

sufiya · Posted 02-03-2018 06:27 PM

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,

art297 · Posted 02-03-2018 06:30 PM

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

sufiya · Posted 02-03-2018 06:34 PM

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;

art297 · Posted 02-03-2018 06:42 PM

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

art297 · Posted 02-03-2018 06:51 PM

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

sufiya · Posted 02-03-2018 07:12 PM

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

Thank you again @art297!!

Classroom Training Available!