Hi--
I'm trying use the Proc SQL Index() function to recode some variables into new variables. My program below does not seem to be working correctly - each line for the case statement is overwriting the value of a previous assignment. Does anyone see what the error is?
Any assistance is appreciated. Thanks.
Proc SQL;
Create Table b.DFAimpr2 as
Select *,
/*** Assign Default ***/
case when AdType='Default' then 1 else 0 end as Default,
/*** Assign Tactic ***/
case when index(Campaign, 'C_AWA') gt 0 then 'Awareness'
when (index(Campaign, 'C_ACQ') gt 0 and index(Placement, 'REMARKETING') gt 0) then 'Acquisition: Remarketing'
when (index(Campaign, 'C_ACQ') gt 0 and index(Placement, 'FTV') gt 0) then 'Acquisition: FTV'
when (index(Campaign, 'C_ACQ') gt 0 and index(Placement, 'iONT') gt 0) then 'Acquisition: iONT'
when index(Campaign, 'C_ACQ') gt 0 then 'Acquisition' else ' ' end as Tactic,
/*** Assign Region ***/
Case
When index(AdName ,'NAT xNY, xDC, xBaltimore, xNorfolk, xRichmond') gt 0 then 'National: Not NY Not Potomac'
When index(AdName ,'NAT xDC, xBaltimore, xNorfolk, xRichmond, xSalisbury') gt 0 then 'National: Not Potomac'
When index(AdName ,'NAT xNY') gt 0 then 'National: Not NY'
When index(AdName ,'NAT xBOS') gt 0 then 'National: Not Boston'
When index(AdName ,'excl BOS') gt 0 then 'National: Not Boston'
When index(AdName ,'NAT xPS') gt 0 then 'National: Not PS'
When index(AdName ,'NAT xPS&NY') gt 0 then 'National: Not PS Not NY'
When index(AdName ,'NY') and not(AdName in ('xNY')) gt 0 then 'New York'
When index(AdName ,'DC, Baltimore, Norfolk, Richmond') gt 0 then 'Potomac'
When index(AdName ,'CA, FL, NJ, NY DMA') gt 0 then 'CA, FL, NJ, NY DMA'
When index(AdName ,'RI, MI, NY, PA, DE, VA, DC, MD') gt 0 then 'RI, MI, NY, PA, DE, VA, DC, MD'
When index(AdName ,'TX') gt 0 then 'TX'
When index(AdName ,'Albany') gt 0 then 'Albany'
When index(AdName ,'Baltimore') gt 0 then 'Baltimore'
When index(AdName ,'BOS') gt 0 then 'Boston'
When index(AdName ,'Boston') gt 0 then 'Boston'
When index(AdName ,'Buffalo') gt 0 then 'Buffalo'
When index(AdName ,'Dallas') gt 0 then 'Dallas'
When index(AdName ,'DC') gt 0 then 'Washington DC'
When index(AdName ,'Harrisburg') gt 0 then 'Harrisburg'
When index(AdName ,'LA') gt 0 then 'Los Angeles'
When index(AdName ,'Norfolk') gt 0 then 'Norfolk' else 'National' end as Region;
quit;
What does your data look like and what do you want?
Is
When index(AdName ,'RI, MI, NY, PA, DE, VA, DC, MD')
supposed to mean when adname contains RI OR MI OR NY OR PA is it looking for that exact string of all the variables together?
SAS(R) 9.2 Language Reference: Dictionary, Fourth Edition
You also seem to be missing a from statement or omitted it from the post.
Yes exactly. If the string contains 'RI, MI, NY, PA, DE, VA, DC, MD' then assign a value of
'RI, MI, NY, PA, DE, VA, DC, MD' to region.
And yes I just neglected to add the FROM
so all are defaulting to whatever is in the ELSE clause?
If so this means that nothing matches and could be because of cases not matching (upper vs lower) or a variety of things.
Very hard to tell without seeing a sample of your data and desired output.
Yes - they are supposed to default to national. Here is a data example
AdName Region
TT | 160x600 | NAT xDC, xBaltimore, xNorfolk, xRichmond, xSalisbury | Email Advertising | 10.6 National
728x90 | NY | 10.6 New York
160x600 | DC, Baltimore, Norfolk, Richmond,| 11.17 Potomac
160x600 | 11.17 | DC, Baltimore, Norfolk, Richmond Potomac
TT | 11.22 | Preroll :30 | Boston Boston
TT | 11.777 | Preroll :30 | Norfolk Norfolk
555x757 | 12.17 | NAT xBOS | 456 National: Not Boston
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.