Hi
Some sample data would have been helpful...
Below some code built on assumptions on how your data might look like.
Why in first place do you query 2 fields to get the month number.
Is this because "Campaign" could be missing? If so then the coalesce() function could be helpful.
However: Below code is doing what I believe you had in mind - which doesn't mean it will give you the desired result... 😉
proc format;
invalue MonthNum
'JAN' = 1
'FEB' = 2
'MAR' = 3
'APR' = 4
'MAY' = 5
'JUN' = 6
'JUL' = 7
'AUG' = 8
'SEP' = 9
'OCT' = 10
'NOV' = 11
'DEC' = 12
otherwise = .
;
quit;
data Tas;
length Campaign $ 20;
do Campaign='JANUARY','MARCH','AUGUST','SEPTEMBER', 'NOVEMBER','DECEMBER';
campaignid+1;
output;
end;
run;
data ProductDef;
length month $ 20;
CampName='dummy';
ProdName='dummy';
do Month='FEBRUARY','APRIL','AUGUST','NOVEMBER';
CampId+1;
output;
end;
Month='OCTOBER';
CampId=10;
output;
run;
%let yy=2010;
proc sql;
/*create table Tas1 as*/
select
a.*,
b.month as ProductDef_Month,
b.CampName,
b.ProdName,
case
when input(substr(b.month,1,3),MonthNum.) ne . then input(substr(b.month,1,3),MonthNum.)
when input(substr(a.Campaign,1,3),MonthNum.) ne . then input(substr(a.Campaign,1,3),MonthNum.)
else 0
end as mth,
&yy as year
from Tas a left join ProductDef b on a.campaignid=b.CampId
where substr(b.CampName,1,1) ^="'" and ProdName ^="CANCEL";
quit;
In regards to the code you've posted:
- A substr() can't start at a minus position "-5"
- A logical condition must be in the when and not in the case (it's like a SAS data step SELECT statement).
- The code with the 2 case statements assigns the values to the same variable - you can't do this in SQL (and what result would you expect from this anyway?)
HTH
Patrick
Message was edited by: Patrick
Message was edited by: Patrick