BookmarkSubscribeRSS Feed
Mgarret
Obsidian | Level 7

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;

5 REPLIES 5
Reeza
Super User

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.

Mgarret
Obsidian | Level 7

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.

Mgarret
Obsidian | Level 7

And yes I just neglected to add the FROM

Reeza
Super User

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.

Mgarret
Obsidian | Level 7

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                          

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!

What is Bayesian Analysis?

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.

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
  • 5 replies
  • 798 views
  • 0 likes
  • 2 in conversation