BookmarkSubscribeRSS Feed
YadiAcho
Calcite | Level 5
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....
3 REPLIES 3
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Calcite | Level 5
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
Opal | Level 21
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 672 views
  • 0 likes
  • 3 in conversation