Help using Base SAS procedures

Proc SQL Index(): Issues Recoding

Reply
Frequent Contributor
Posts: 144

Proc SQL Index(): Issues Recoding

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;

Super User
Posts: 19,789

Re: Proc SQL Index(): Issues Recoding

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.

Frequent Contributor
Posts: 144

Re: Proc SQL Index(): Issues Recoding

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.

Frequent Contributor
Posts: 144

Re: Proc SQL Index(): Issues Recoding

And yes I just neglected to add the FROM

Super User
Posts: 19,789

Re: Proc SQL Index(): Issues Recoding

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.

Frequent Contributor
Posts: 144

Re: Proc SQL Index(): Issues Recoding

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                          

Ask a Question
Discussion stats
  • 5 replies
  • 223 views
  • 0 likes
  • 2 in conversation