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

[sufiya]

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

[Reeza]

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]

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]

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]

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

Thank you again for your help!  🙂

[art297 · Accepted solution]

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]

Thank you art297!! 🙂 it worked

[sufiya]

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]

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]

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]

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]

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]

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]

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]

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

Thank you again @art297!!