Function Substr with OR

[YadiAcho]

Need help.....!!!

I want to classified data with function substr and sql procedure...

This code can't solve my problem.....

================================================
proc sql;
create table Tas1 as
select a.*, b.CampName,b.ProdName
, case substr(a.Campaign, -5,3)
when 'JAN' then 1
when 'FEB' then 2
when 'MAR' then 3
when 'APR' then 4
when 'MAY' then 5
when 'JUN' then 6
when 'JUL' then 7
when 'AUG' then 8
when 'SEP' then 9
when 'OCT' then 10
when 'NOV' then 11
when 'DEC' then 12
else 0 end as mth
, case substr(b.MONTH, 1,3)
when 'JAN' then 1
when 'FEB' then 2
when 'MAR' then 3
when 'APR' then 4
when 'MAY' then 5
when 'JUN' then 6
when 'JUL' then 7
when 'AUG' then 8
when 'SEP' then 9
when 'OCT' then 10
when 'NOV' then 11
when 'DEC' then 12
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;

or this....

========================================================
proc sql;
create table Tas1 as
select a.*, b.CampName,b.ProdName
, case substr(b.MONTH, -5,3) or substr(b.MONTH, 1,3)
when 'JAN' then 1
when 'FEB' then 2
when 'MAR' then 3
when 'APR' then 4
when 'MAY' then 5
when 'JUN' then 6
when 'JUL' then 7
when 'AUG' then 8
when 'SEP' then 9
when 'OCT' then 10
when 'NOV' then 11
when 'DEC' then 12
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;

======================================

anyone can help me......

Thx a lot.....

regards,

Yadi Acho....

[sbb]

In your PROC SQL and the SELECT, you can use a combination of string-constant data values concatenated with suitable input data substring, then run it through the appropriate INFORMAT. Similar DATA step example listed below; also works with PROC SQL.

Scott Barry
SBBWorks, Inc.


26 data _null_;
27 retain Campaign 'NOV....';
28 month = month(input('01'!!SUBSTR(Campaign,1,3)!!'00',date.));
29 putlog _all_;
30 run;

Campaign=NOV.... month=11 _ERROR_=0 _N_=1
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

[YadiAcho]

Thx a lot...

But I can't understand what this input means.... you can tell me why using this...

'01'!!...... !!'00'.....

thx for your advice...

Acho...

[Patrick]

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