topic Re: Proc SQL Index(): Issues Recoding in SAS Procedures

Proc SQL Index(): Issues Recoding

Mgarret — Mon, 09 Dec 2013 22:21:40 GMT

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;

Re: Proc SQL Index(): Issues Recoding

Reeza — Mon, 09 Dec 2013 22:30:45 GMT

What does your data look like and what do you want?

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.

Re: Proc SQL Index(): Issues Recoding

Mgarret — Mon, 09 Dec 2013 22:41:55 GMT

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.

Re: Proc SQL Index(): Issues Recoding

Mgarret — Mon, 09 Dec 2013 22:42:46 GMT

And yes I just neglected to add the FROM

Re: Proc SQL Index(): Issues Recoding

Reeza — Mon, 09 Dec 2013 22:53:58 GMT

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.

Re: Proc SQL Index(): Issues Recoding

Mgarret — Mon, 09 Dec 2013 23:07:45 GMT

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