Desktop productivity for business analysts and programmers

Function Substr with OR

Reply
Occasional Contributor
Posts: 8

Function Substr with OR

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....
Super Contributor
Super Contributor
Posts: 3,174

Re: Function Substr with OR

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
Occasional Contributor
Posts: 8

Re: Function Substr with OR

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...
Respected Advisor
Posts: 4,137

Re: Function Substr with OR

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
Ask a Question
Discussion stats
  • 3 replies
  • 139 views
  • 0 likes
  • 3 in conversation