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 |
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
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.
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 |
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
not familiar at all with SAS Proc Transpose, will read up on it.
Thank you again for your help! 🙂
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
Thank you art297!! 🙂 it worked
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
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
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,
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
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;
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
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
ok, so tried it again and no errors this time @_@
Thank you again @art297!!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.