DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Contributor
Posts: 34
Accepted Solution

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

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

BookStart_DATEItem ValueDate_BoughtBought
ItemA01-Oct-171301-Nov-179
ItemB01-Oct-171401-Oct-1714
ItemD01-Oct-171501-Nov-1712
ItemE01-Oct-172001-Nov-1713
ItemF01-Oct-171201-Oct-1714
ItemG01-Oct-171401-Dec-1615
ItemH01-Oct-171401-Oct-1716
ItemI01-Oct-171501-Oct-1717
ItemJ01-Oct-171801-Dec-1618
ItemK01-Oct-172501-Nov-1719
ItemL01-Oct-171301-Dec-1620

 

 

End Result- want to achieve this:

BookStart_DATEItem ValueDate_Bought_OctoberDate_Bought_NovemberDate_Bought_December
ItemA01-Oct-1713090
ItemB01-Oct-17141400
ItemD01-Oct-17150120
ItemE01-Oct-17200130
ItemF01-Oct-17121400
ItemG01-Oct-17140015
ItemH01-Oct-17141600
ItemI01-Oct-17151700
ItemJ01-Oct-17180018
ItemK01-Oct-17250190
ItemL01-Oct-17130020

 


Accepted Solutions
Solution
‎02-03-2018 06:02 PM
PROC Star
Posts: 8,115

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

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


All Replies
Super User
Posts: 22,873

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

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. 

Contributor
Posts: 34

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

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

BookStart_DATEItem ValueDate_BoughtBought
ItemA01-Oct-171301-Nov-179
ItemB01-Oct-171401-Oct-1714
ItemD01-Oct-171501-Nov-1712
ItemE01-Oct-172001-Nov-1713
ItemF01-Oct-171201-Oct-1714
ItemG01-Oct-171401-Dec-1615
ItemH01-Oct-171401-Oct-1716
ItemI01-Oct-171501-Oct-1717
ItemJ01-Oct-171801-Dec-1618
ItemK01-Oct-172501-Nov-1719
ItemL01-Oct-171301-Dec-1620

 

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:

BookStart_DATEItem ValueDate_Bought_OctoberDate_Bought_NovemberDate_Bought_December
ItemA01-Oct-1713090
ItemB01-Oct-17141400
ItemD01-Oct-17150120
ItemE01-Oct-17200130
ItemF01-Oct-17121400
ItemG01-Oct-17140015
ItemH01-Oct-17141600
ItemI01-Oct-17151700
ItemJ01-Oct-17180018
ItemK01-Oct-17250190
ItemL01-Oct-17130020

 

Super User
Posts: 22,873

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

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

 


 

Contributor
Posts: 34

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

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

Thank you again for your help!  Smiley Happy

Solution
‎02-03-2018 06:02 PM
PROC Star
Posts: 8,115

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

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

 

Contributor
Posts: 34

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

Thank you art297!! Smiley Happy it worked

 

 

Contributor
Posts: 34

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

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

 

 

 

PROC Star
Posts: 8,115

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

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

 

Contributor
Posts: 34

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

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,
PROC Star
Posts: 8,115

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

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

 

Contributor
Posts: 34

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

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;

 

PROC Star
Posts: 8,115

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

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

 

PROC Star
Posts: 8,115

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

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

 

Contributor
Posts: 34

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

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

Thank you again @art297!! 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 19 replies
  • 232 views
  • 1 like
  • 4 in conversation